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

 

[Upula]

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.

 

[caabyyc]

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)))))

 

[TheNatural]

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.

 

[tusharm]

See Generate All Permutations.

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

 

[seepra]

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..

 

[RoxanaR]

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!

 

[TemiA]

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

 

[bschwartz]

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

Love this trick.

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

 

[Scotty81]

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

 

[Axe63]

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

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.