Every Combination of string from 4 columns

[leebird]

Hello,

I have 4 columns of account segments. I would like have every possible combination of strings that can be assembled. The order of assembly should be......... segment 1 - segment 2 - segment 3 - segment 4
Would it also be possible to have the output in this format:

Example: 10-200-50020-7900

Thank you very much

[shg]

That's 2.4 million combinations. What would you do if you had them all?

[CK76]

Not sure the purpose either. But you'd need what's called Cartesian Product/Join.

Easiest way to do it is to use PowerQuery (since you have Excel 2010).

Convert each list to table. Load Segment 1 to PowerQuery.

Add custom column with following M.

Please Login or Register  to view this content.

Where Name = Table name & [Segment 2] is the column name.

Repeat for rest of the lists.

Result should look like below.
cartesian.JPG

Expand each column. Convert data type to TEXT.

Add Custom column and concatenate all columns.

Please Login or Register  to view this content.

Remove all but the concatenated column.

Now you have the table.

However, since it has more rows than can be loaded to a sheet, you will need to filter and split table into multiple and load.

[YasserKhalil]

try this code (from HansV from ElieensLounge forum)

Please Login or Register  to view this content.

[leebird]

We are looking to see how many account strings would be possible if we linked all the segments together. Just out of curiosity, how did you calculate to 2.4M? This is in itself is useful to us.

[Pete_UK]

... Just out of curiosity, how did you calculate to 2.4M? ...

Try this formula in cell F1:

=(COUNTA(A:A)-1) * (COUNTA(B:B)-1) * (COUNTA(C:C)-1) * (COUNTA(D:D)-1)

(the -1 is because you have headers in each column). The number of combinations is the product of the number of individual items in each column.

Hope this helps.

Pete

[leebird]

Excellent. Thank you so much. Appreciate all the help!

[shg]

... how did you calculate to 2.4M? This is in itself is useful to us.

It's the product of the number of entries in each column: =8*10*209*144