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

 

[trunght12]

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.

Thanks, could you give me the formua for 3-4-5 collumn? Thanks!!!

 

[Eric W]

Welcome to the forum!

FYI, it's usually best to open a new thread with a question. If you add a message to an old thread, it's a big gamble as to whether someone will actually see it. tusharm hasn't posted anything for over 4 years, so it's unlikely he'll reply now.

Next, here's a formula that should work for multiple columns:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		One	Two	Three	Four	Five
3	x	a	x	cat	1	5		a	x	cat	1	5
4		b	y	dog	2			b	x	cat	1	5
5		c			3			c	x	cat	1	5
6								a	y	cat	1	5
7								b	y	cat	1	5
8								c	y	cat	1	5
9								a	x	dog	1	5
10								b	x	dog	1	5
11								c	x	dog	1	5
12								a	y	dog	1	5
13								b	y	dog	1	5
14								c	y	dog	1	5
15								a	x	cat	2	5
16								b	x	cat	2	5
17								c	x	cat	2	5
18								a	y	cat	2	5
19								b	y	cat	2	5
20								c	y	cat	2	5
21								a	x	dog	2	5
22								b	x	dog	2	5
23								c	x	dog	2	5
24								a	y	dog	2	5
25								b	y	dog	2	5
26								c	y	dog	2	5
27								a	x	cat	3	5
28								b	x	cat	3	5
29								c	x	cat	3	5
30								a	y	cat	3	5
31								b	y	cat	3	5
32								c	y	cat	3	5
33								a	x	dog	3	5
34								b	x	dog	3	5
35								c	x	dog	3	5
36								a	y	dog	3	5
37								b	y	dog	3	5
38								c	y	dog	3	5
Sheet1

Cell Formulas
Range	Formula
H3:L38	H3	{=IF(ROW()>PRODUCT(SUBTOTAL(3,OFFSET($B$3:$B$20,0,COLUMN($B:$F)-COLUMN($B:$B))))+ROW($B$3)-1,"",INDEX(B:B,MOD(INT((ROW(H3)-ROW(H$3))/PRODUCT(SUBTOTAL(3,OFFSET($A$3:$A$20,0,COLUMN($H:H)-COLUMN($H:$H))))),COUNTA(B$3:B$20))+ROW(B$3)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

You just need to make sure that there is one value in column A to make sure the formula works. Then copy the formula down and over as needed. To make it work with a different number of columns or rows, just adjust the ranges as needed. Or you can put a single element in the E and/or F columns and then ignore those columns in the output.

Hope this helps!

 

[Mennodus]

Just use Power Query > Transform > Unpivot columns. Listing all possible combinations is extremely slow and error-prone. But if you use Power Query it is all done automatically.

 

[colink2]

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.

@erangi
This is a fantastic formula. I cannot figure out how to add a space bewteen the resulting list so I want:
So to use the example

Column A	Column B		Output (Combinations)
Mar	AA		MarAA

I want

Column A	Column B		Output (Combinations)
Mar	AA		Mar AA

I would also like to do a result in reverse order

Column A	Column B		Output (Combinations)
Mar	AA		AA Mar

Any hlp modifying this formula for both scenarias would be apreciated.

ColinK2

 

[Eric W]

To add a space:

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

To reverse the order:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))&" "&INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))

 

[colink2]

To add a space:

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

To reverse the order:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))&" "&INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))

Great Eric W - It works perfectly.

I wish I was smart enouigh to figure out how it works

I read your comments on this page about this type of formula getting cumbersome and managed to get your Macro working.
Much more flexibility, can easily be edited for a Max number of roows and columns and order + it ignores cells with null or " " data

Cartesian using VBA Macro

This must be the best forum for Excel help compared to any IT forums.

Everytime I post a question I get a helpful reply.

I could nto consider asking this question on the unfreindly StackExchange were it seems users are supposed to know the answer before they ask the question and were there is no time for non experts.

 

[Eric W]

Glad I could help!

I don't spend much time on other forums, so I can't really compare. I do know that the moderators here spend a lot of effort promoting civility and collegiality. We get questions here from absolute beginners to people who have used Excel for decades and can't quite figure out something. Everyone's treated respectfully.