Make All Possible Permutations

[jared999]

You guys were so helpful with my first question, I have another one:

I have two arrays with two columns each

The first array is Part Number and Part Name
The second array is Color Code and Color Name

What I'm looking to do is make one array with all the possible combinations of the two arrays. I know how to do this manually with concatenation, but it is a long list of both Parts and Colors.

See the attached spreadsheet with my starting point and end goal (highlighted).

Demo Spreadsheet 2.xlsx

Thanks,

Jared

[abousetta]

Hi,

This should replicate your desired output:

Please Login or Register  to view this content.

abousetta

[Winon]

@ abousetta,

Very nice!

Just a small "Typo" here:

Please Login or Register  to view this content.

Should be "Part Name"

[abousetta]

Hi Winon,

Yes, you're right... I updated the code above.

Honestly the code could have been more efficient by converting the ranges to arrays, looping through the arrays creating a third array using the Scripting dictionary and printing the keys on the sheet, but it's too late at night for me to be coding arrays right now . Plus unless the list is massive, the OP won't feel the difference in execution time.

abousetta

[newdoverman]

I used a helper sheet to get the individual elements of the desired results into separate columns then concatenated the columns back on sheet1.

[john456852]

to check, use this formula:


nPk=n!/(n-k)!

n= total amount and k= number of cells you need to choose. there should be (n!/(n-k)!) ways