Thread: How to generate all possible combinations of two lists (without MACRO)?

1. How to generate all possible combinations of two lists (without MACRO)?

Hi everyone,

I need to make a list of all possible combinations of two lists as shown in the following example.
i.e. Basically when I update Column A & B, the combinations should get appear in the “Output” column.
Also need to do this without running a MACRO.

Can someone kindly help me?

Example
 Column A Column B Output (Combinations) Mar AA MarAA Apr BB MarBB May CC MarCC DD MarDD AprAA AprBB AprCC AprDD MayAA MayBB MayCC MayDD

2. Re: How to generate all possible combinations of two lists (without MACRO)?

hi,

A Cartesian product via a query table could be used. No formulas, no code.

Such as, if the first table (including header "Column A") is given normal defined name "A" and the second table "B", then the SQL would be
Code:
```SELECT A.[Column A] & B.[Column B]
FROM A, B```
Briefly. Create the defined names, save the file then create the query (ALT-D-D-N) by following the wizard. At the last step of the wizard choose the option to edit in MS Query and change the SQL to above. Exit MS Query to complete the query table. Please google for further explanation or ask again.

regards

3. Re: How to generate all possible combinations of two lists (without MACRO)?

Then, in some empty cell, say F1 enter the formula =IF(ROW()-ROW(\$F\$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW(\$F\$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW(\$F\$1),COUNTA(B:B))+1))

Copy F1 down col. F until you get cells that look empty.

4. Re: How to generate all possible combinations of two lists (without MACRO)?

Dear tusharm,

You are a GOD. This is exactly what I was looking for. Thanks a lot.

5. Re: How to generate all possible combinations of two lists (without MACRO)?

You are welcome.
Originally Posted by erangai
Dear tusharm,

You are a GOD. This is exactly what I was looking for. Thanks a lot.

6. Re: How to generate all possible combinations of two lists (without MACRO)?

Originally Posted by tusharm
You are welcome.
Dear tusharm,

I just tried to understand how you have given the formula to improve my knowledge & not to bother you again on a similar incident again (LOL!)

I can figureout the start & the end but the part in red below is bit clueless. Can you pls explain it for everyone to improve their excel skills pls...

IF(ROW()-ROW(\$E\$1)+1>COUNTA(B:B)*COUNTA(C:C),"",INDEX(B:B,INT((ROW()-ROW(\$E\$1))/COUNTA(C:C)+1))&INDEX(C:C,MOD(ROW()-ROW(\$E\$1),COUNTA(C:C))+1))

This is to arrive at how many times an item in the cloumn A, should repeat right?
In my below ex, "Mar" should be repeated four times since there are four items in the cloumn B.

So ROW()-ROW(\$E\$1) = i.e. if you are at N th row, this gives the value of N-1 (at 10th row this gives the value 9)
COUNTA(C:C)
= i.e. the number of items in cloumn B list (in the below example it is 4)
So at 10th row, (ROW()-ROW(\$E\$1))/COUNTA(C:C)+1) means (9/4)+1 = 2.250+1 = 3.250
So when you take only the integer this will give the answer 3, so at 10th row, the 3rd item in cloumn A (which is May) needs to get appeared.
I understand this part but the mathematical logic behind this is what I'm not very clear about.

7. Re: How to generate all possible combinations of two lists (without MACRO)?

I'm guessing not relevant here because it seems a formula is preferred - quite a contrast, a formula that isn't understood and the simple SQL.

SELECT A.[Column A] & B.[Column B]
FROM A, B

8. Re: How to generate all possible combinations of two lists (without MACRO)?

That's something I've been doing very effectively for a long time. The idea is this. If you have 2 lists that you want to pick from so that you get every possible combination, you want to select 1 item from list A and combine it with every item in list B, then select the next item in list A and combine it with every item in list B.

The way to loop through a list is to use a function that generates 1,2,3,...,N,1,2,3,...,N,... This is close to what the MOD function does. It generates values 0,1,2,...N-1,0,1,2,...,N-1,...

So, ROW()-ROW(1st row) will generate 0,1,2,3,... and MOD(,N) will yield 0,1,2,...N-1,0,1,2,...,N-1,... Add 1 to that and you get the desired sequence.

Now, for list A you want to increment the index only after every item in list B has been selected, which will happen every N rows. So, INT(ROW()-ROW(1st row) / N) will jump from one integer to the next every N rows.

You can extend this concept to any number of lists A, B, C, D,...See
Generate All Permutations
Generate All Permutations
Originally Posted by erangai
Dear tusharm,

I just tried to understand how you have given the formula to improve my knowledge & not to bother you again on a similar incident again (LOL!)

I can figureout the start & the end but the part in red below is bit clueless. Can you pls explain it for everyone to improve their excel skills pls...

IF(ROW()-ROW(\$E\$1)+1>COUNTA(B:B)*COUNTA(C:C),"",INDEX(B:B,INT((ROW()-ROW(\$E\$1))/COUNTA(C:C)+1))&INDEX(C:C,MOD(ROW()-ROW(\$E\$1),COUNTA(C:C))+1))

This is to arrive at how many times an item in the cloumn A, should repeat right?
In my below ex, "Mar" should be repeated four times since there are four items in the cloumn B.

So ROW()-ROW(\$E\$1) = i.e. if you are at N th row, this gives the value of N-1 (at 10th row this gives the value 9)
COUNTA(C:C)
= i.e. the number of items in cloumn B list (in the below example it is 4)
So at 10th row, (ROW()-ROW(\$E\$1))/COUNTA(C:C)+1) means (9/4)+1 = 2.250+1 = 3.250
So when you take only the integer this will give the answer 3, so at 10th row, the 3rd item in cloumn A (which is May) needs to get appeared.
I understand this part but the mathematical logic behind this is what I'm not very clear about.

9. Re: How to generate all possible combinations of two lists (without MACRO)?

Originally Posted by Fazza
I'm guessing not relevant here because it seems a formula is preferred - quite a contrast, a formula that isn't understood and the simple SQL.

SELECT A.[Column A] & B.[Column B]
FROM A, B

I tried and it worked like a charm.

Very nice solution!

M.

10. Re: How to generate all possible combinations of two lists (without MACRO)?

Originally Posted by tusharm
That's something I've been doing very effectively for a long time. The idea is this. If you have 2 lists that you want to pick from so that you get every possible combination, you want to select 1 item from list A and combine it with every item in list B, then select the next item in list A and combine it with every item in list B.

The way to loop through a list is to use a function that generates 1,2,3,...,N,1,2,3,...,N,... This is close to what the MOD function does. It generates values 0,1,2,...N-1,0,1,2,...,N-1,...

So, ROW()-ROW(1st row) will generate 0,1,2,3,... and MOD(,N) will yield 0,1,2,...N-1,0,1,2,...,N-1,... Add 1 to that and you get the desired sequence.

Now, for list A you want to increment the index only after every item in list B has been selected, which will happen every N rows. So, INT(ROW()-ROW(1st row) / N) will jump from one integer to the next every N rows.

You can extend this concept to any number of lists A, B, C, D,...See
Generate All Permutations
Generate All Permutations

Thanks a lot for your kind support... I'm new to this Forum & really looking forward for your help in future as well

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•