A while back I wanted to create a new part number scheme for an existing product. It sounded simple at first. 78 different models, and only 7 options. I wanted the new part number to be ‘intelligent’ in that each digit or group of digits meant a certain option choice. The problem is that within the options, you could have several different choices! 7 quickly balloned out to over 100,000!
I knew this was the stuff databases were made for, but I also felt sure I could pull it off in excel as well. (No Visual Basic required, as I don’t do any programming!)
After a couple of days on the web with my friend google, I finally found a solution, what I was trying to do was called ‘The Cartesian Product’. The solution I found is posted below:
I want to produce a list from two arrays.A 1B 2C 3I want the result to beA1A2A3B1B2B3C1C2C3This is referred to as obtaining the Cartesian product. Here'sthe way I know how to do it. This uses MSQUERY, which doesn'tget a lot of press but is very useful for manipulation of data(Excel and otherwise) using SQL. You don't actually need to knowSQL to do this though.
Set up a little worksheet like this cornered at A1:
First_Value Second_ValueA 1B 2C 3
Create two named ranges likeFirst : refers to =Sheet1!$A$1:$A$4Second : refers to =Sheet1!$B$1:$B$4
Save the workbook.
Data | Import External Data | New Database Query
In the "Choose Data Source" dialog, select "Excel Files*" | OK
Navigate to the file you saved. If everything is correct so far,you will see "First" and "Second" in "Available tables and columns".Use the chevron > to add each to the "Columns in your query". Next.The query wizard complains that it can not join the tables, etc. OK.The query editor will open and should display the kind of resultsyou are looking for. So, click File | Return Data to Microsoft Excel.
Now choose (click) a place to drop the query results, such as D1.
The results should look like
First_Value Second_ValueA 1B 1C 1A 2B 2C 2A 3B 3C 3
The results can then be concatenated with the usual methods, e.g.,=CONCATENATE(D7;E7) C2
Hats off to ‘hande’, whoever they may be! This worked exactly like I wanted. The problem I quicky ran in to however was that my options exceeded the 65,000 row limit in the version of excel I was using. (2007, I think, now allows something like a million from what I’ve heard) To find out if you’re going to break the limit in excel just multiply the number of each option times each other. In the example above its simply 3X3=9 unique numbers.
In my case Option 1 (the model lines) had 78 items, Option 2 had 3, Option 3 had 2, and on and on. (101,090 total unique products in my case!) I got around my limitation by leaving out one of the two(2) item options thus cutting my results in half. To solve that, I simply copied the resulting part numbers over and had one column of Option X and one column where I set it to Option Y.
After a couple of off/on days work, I now have an excel file that I can use pull downs to select options and create valid part numbers/optioned products. Each number or group represents a logical option. This number is compared to an edited list (from the 101,000+) that now only contains valid numbers.
A hint here, if you use a mix of alpha and numeric values for your option codes, it will help break a part a model number. All numbers are bad because someone can leave one out and change the whole model. If you mix in a letter here or there it’ll help. Also, with only numbers you have 0-9, text has 23-24 (leave out the ‘O’, ‘I’, and ‘L’ as they may get confusing: i1l or 0oO), plus text can actually equal options R=Red, B=Blue and make even more sense.
Share on Facebook
]]>
