How To: Permutations from columns with various data

[actruckin]

I have to come up with a list of part numbers for my company and I have to come up with all the permutations for part numbers. I started to think that this was a simple manual task until I laid it out and realized the total number of permutations that would be listed. I'm asking for some help on how to set this up if anyone can help.

The (*) in the last column is a place holder that could be a blank (should be) in some part numbers. The whole part number would read from left to right. Any help would be appreciated.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]M[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]H[/TD]
[TD]5[/TD]
[TD]A[/TD]
[TD]*[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15
[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]C[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]C[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]D[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]E[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]52[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[PaddyD]

Am I reading this right:

part numbers are 10 character alpha-numeric strings
First position - always an M
Second position - one of 13,15,17,20,23,26,30,32,42,52,60
Third position - always a 1
Forth position - one of 1,2,3,4,5,6

etc.. And the task is to list all possible part numbers given the above seeds?

& if that's not it, can you describe again...

 

[actruckin]

PaddyD, that is correct.

 

[PaddyD]

Job for vba - not really my thing, but sure someone will pick this up...

 

[actruckin]

Thanks so much for looking at this. I appreciate it.

 

[shg]

See the workbook at https://www.box.com/s/47b28f19d794b25511be

 

[actruckin]

Thanks so much. I'm looking at this now to dissect the info. Thanks again!

 

[shg]

You're welcome, good luck.

 

[SydneyGeek]

Sticking my oar in, but you don't need VBA for this -- you can use MS Query, with the correct setup.
Do the following (I hope you have Excel 2007 or higher or you will run out of rows for the output...)
Place the contents of each column on a new sheet, with the generic headers Heading1 to Heading9 (one heading per sheet, change the heading text for each sheet).
Make sure there are no blank rows or columns, then save the file.
New file, save as the output file and launch MS Query. Pick Excel Files as the file type, navigate to the file you created with the multiple tabs, OK.
In sequence pick the sheets in the order you want to see the columns, then click the > button to add them to the grid. You should see Heading1 to Heading9 lined up below each other. Click OK, then OK for the warning message about not being able to join, then close MS Query (the outer one of the two windows).
You will be prompted to select the cell for the output. Pick A1 on your new file, wiat a while and you will have all permutations.
Now it's a simple case of going to J2 and entering =A2&B2&...&I2. Press ENTER; if you have 2007 or higher you have a data table layout, and all rows in column J will fill with the part nos.
When I ran it the output was around 180 K rows.

Denis