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

erangai

New Member
Joined
Aug 22, 2012
Messages
6
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
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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

:)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top