All possible combinations but sperate the combinations into two columns (No VBA)

[andrew_milonavic]

Hi,

Awhile ago @wideboydixon helped me and built a formula to combine all possible combinations without duplicates or reverse order duplicates.

Previous thread: https://www.mrexcel.com/forum/excel...-single-column-without-vba-2.html#post4750264

Is it possible to separate the combinations into two columns, D and E instead of just D?

	A	B	C	D
1	Ant	1	2	Ant,Bear
2	Bear	1	3	Ant,Cat
3	Cat	1	4	Ant,Dog
4	Dog	1	5	Ant,Elephant
5	Elephant	1	6	Ant,Frog
6	Frog	1	7	Ant,Gorilla
7	Gorilla	2	3	Bear,Cat
8		2	4	Bear,Dog
9		2	5	Bear,Elephant
10		2	6	Bear,Frog
11		2	7	Bear,Gorilla
12		3	4	Cat,Dog
13		3	5	Cat,Elephant
14		3	6	Cat,Frog
15		3	7	Cat,Gorilla
16		4	5	Dog,Elephant
17		4	6	Dog,Frog
18		4	7	Dog,Gorilla
19		5	6	Elephant,Frog
20		5	7	Elephant,Gorilla
21		6	7	Frog,Gorilla

<thead>
</thead><tbody>
</tbody>

Cell	Formula
B1	=IF(COUNTA(A:A)>1,1,"")
C1	=IF(COUNTA(A:A)>1,2,"")
D1	=IF(B1="","",INDEX(A:A,B1)&","&INDEX(A:A,C1))
B2	=IF(ROW()>COUNTA(A:A)*(COUNTA(A:A)-1)/2,"",IF(C1=COUNTA(A:A),B1+1,B1))
C2	=IF(ROW()>COUNTA(A:A)*(COUNTA(A:A)-1)/2,"",IF(C1=COUNTA(A:A),B2+1,C1+1))

<thead>
</thead><tbody>
</tbody>

Thanks

Andrew

 

[Scott T]

Excel 2010
	A	B	C	D	E
1	Ant	1	2	Ant	Bear
2	Bear	1	3	Ant	Cat
3	Cat	1	4	Ant	Dog
4	Dog	1	5	Ant	Elephant
5	Elephant	1	6	Ant	Frog
6	Frog	1	7	Ant	Gorilla
7	Gorilla	2	3	Bear	Cat
8		2	4	Bear	Dog
9		2	5	Bear	Elephant
10		2	6	Bear	Frog
11		2	7	Bear	Gorilla
12		3	4	Cat	Dog
13		3	5	Cat	Elephant
14		3	6	Cat	Frog
15		3	7	Cat	Gorilla
16		4	5	Dog	Elephant
17		4	6	Dog	Frog
18		4	7	Dog	Gorilla
19		5	6	Elephant	Frog
20		5	7	Elephant	Gorilla
21		6	7	Frog	Gorilla
Sheet4

Cell Formulas
Range	Formula
D1	=IF(B1="","",INDEX(A:A,B1))
D2	=IF(B2="","",INDEX(A:A,B2))
D3	=IF(B3="","",INDEX(A:A,B3))
D4	=IF(B4="","",INDEX(A:A,B4))
D5	=IF(B5="","",INDEX(A:A,B5))
D6	=IF(B6="","",INDEX(A:A,B6))
D7	=IF(B7="","",INDEX(A:A,B7))
D8	=IF(B8="","",INDEX(A:A,B8))
D9	=IF(B9="","",INDEX(A:A,B9))
D10	=IF(B10="","",INDEX(A:A,B10))
D11	=IF(B11="","",INDEX(A:A,B11))
D12	=IF(B12="","",INDEX(A:A,B12))
D13	=IF(B13="","",INDEX(A:A,B13))
D14	=IF(B14="","",INDEX(A:A,B14))
D15	=IF(B15="","",INDEX(A:A,B15))
D16	=IF(B16="","",INDEX(A:A,B16))
D17	=IF(B17="","",INDEX(A:A,B17))
D18	=IF(B18="","",INDEX(A:A,B18))
D19	=IF(B19="","",INDEX(A:A,B19))
D20	=IF(B20="","",INDEX(A:A,B20))
D21	=IF(B21="","",INDEX(A:A,B21))
E1	=IF(B1="","",INDEX(A:A,C1))
E2	=IF(B2="","",INDEX(A:A,C2))
E3	=IF(B3="","",INDEX(A:A,C3))
E4	=IF(B4="","",INDEX(A:A,C4))
E5	=IF(B5="","",INDEX(A:A,C5))
E6	=IF(B6="","",INDEX(A:A,C6))
E7	=IF(B7="","",INDEX(A:A,C7))
E8	=IF(B8="","",INDEX(A:A,C8))
E9	=IF(B9="","",INDEX(A:A,C9))
E10	=IF(B10="","",INDEX(A:A,C10))
E11	=IF(B11="","",INDEX(A:A,C11))
E12	=IF(B12="","",INDEX(A:A,C12))
E13	=IF(B13="","",INDEX(A:A,C13))
E14	=IF(B14="","",INDEX(A:A,C14))
E15	=IF(B15="","",INDEX(A:A,C15))
E16	=IF(B16="","",INDEX(A:A,C16))
E17	=IF(B17="","",INDEX(A:A,C17))
E18	=IF(B18="","",INDEX(A:A,C18))
E19	=IF(B19="","",INDEX(A:A,C19))
E20	=IF(B20="","",INDEX(A:A,C20))
E21	=IF(B21="","",INDEX(A:A,C21))

 

[andrew_milonavic]

Hey Scott,

Thanks for the fast reply!!

I'll give it a try

Thanks

Andrew

 

[andrew_milonavic]

Thanks Scott! Works perfect

Andrew