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

[erangai]

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

 

[Fazza]

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

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

 

[tusharm]

Suppose the A and B data start with row 1.

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.

 

[erangai]

Dear tusharm,

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

 

[tusharm]

You are welcome.

Dear tusharm,

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

 

[erangai]

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.

 

[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

 

[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

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.

 

[Marcelo Branco]

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.

 

[erangai]

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