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:
I have a slightly different problem. I need to list all possible combination totals of given set of numbers.
For example if I have 3 currency notes of , say Rs 1, Rs 5 and Rs 10 then I can use them to pay exactly the following 7 amounts:
(All amounts in Rs)
1, 5, 10 (using 1 currency note,
6, 15, 11 (using 2 currency note) and
16 (using all three currency notes.

How can I list these using an excel formula for n items valued v1, v2,...vn respectively.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
For formula practice only, in C1, type following then drag it down until you see blank

=IF(ROW(A1)>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,IF(MOD(ROW(A1),COUNTA(B:B))=0,ROW(A1)/COUNTA(B:B),INT(ROW(A1)/COUNTA(B:B))+1))&INDEX(B:B,IF(MOD(ROW(A1),COUNTA(B:B))=0,COUNTA(B:B),MOD(ROW(A1),COUNTA(B:B)))))
 
Upvote 0
How can I add a third column to make even more combinations?
Oh and one last thing... is there any way to add a space between the combinations :/ ?


Thanks a bunch

-TheNatural



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.
 
Upvote 0
Thanks a lot for your kind support... I'm new to this Forum & really looking forward for your help in future as well

Hello,
i would like to know the formula for genearting combinations from only one list...for example from a list of jan, feb, march, apr, .....,
a list of possible combinations would be jan feb, jan march, jan apr,feb mar,feb apr,feb may, ........
thank you..
 
Upvote 0
tusharm, the formula above was very useful but now I have to generate all the possible combinations from a large number of columns :( Can you please help me with this and let me know how the formula would look like if for instance we had 3 columns?
Many thanks!
 
Upvote 0
hey everyone,
please i need help in generating scenarios. for example i have 5 demands with 2 possible outcomes High and low with two probabilities
FOR EXAMPLE;
Input data in excel
demand Low High Low high
1 70 82 0.3 0.7
2 65 75 0.3 0.7
3 53 60 0.3 0.7
4 10 26 0.5 0.5
5 54 66 0.5 0.5

i want the output scenario to be something like

Output scenario
scenario demand 1 demand 2 demand 3 demand 4 demand 5 probabiity
1
2
3
please how can i go about it? im so confused please someone help
 
Upvote 0
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

Love this trick.

Simple and elegant. Did exactly what I needed, even with multiple columns.
 
Upvote 0
Hi,

Is there any way to put a delimiter between the data in column A and B? That is, rather than listing just MarAA, which is textually very dense, can the output be: Mar.AA in which the information in each column is separated by a . (period). I realize that one cheat I can do is to append a period to the data in the first column and that combine that appended column to the 2nd column. I was just looking for a tweak in the formula, if that was possible.

Thanks in advance.

Scotty81
 
Upvote 0
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

I like this solution. Had some difculty defining the list correctly with Excel 2010. I don't think it liked my initial table formatting. In the end I used:

SELECT Country.Country, Product.Product
FROM Country Country, Product Product

Where each list was named the same as the header (just to confuse me).
To name each lists I selected each list and pressed Ctr + Sht + F3

However I have just discovered the MS Excel Add-on "Power Query" and I am exited to try and get this Select query to work.
Would anyone be able to offer me a simple SQL Power Query guide to generating this solution. My specific application is I need to register a number of products in a number of countries. I have one list of countries headed "Countries" and a separate list of all products headed "Products". I then want to generate a table comprising of two columns with all combinations of Country and Product.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
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