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
Ant	Ant,Bear
Bear	Ant,Cat
Cat	Ant,Dog
Dog	Ant,Elephant
Elephant	Ant,Frog
Frog	Ant,Gorilla
Gorilla	Bear,Cat
Bear,Dog
Bear,Elephant
Bear,Frog
Bear,Gorilla
Cat,Dog
Cat,Elephant
Cat,Frog
Cat,Gorilla
Dog,Elephant
Dog,Frog
Dog,Gorilla
Elephant,Frog
Elephant,Gorilla
Frog,Gorilla

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

</tbody>

<thead>
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]

</thead><tbody>
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF( COUNTA(A:A)>1,1,"" )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=IF( COUNTA(A:A)>1,2,"" )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=IF( B1="","",INDEX(A:A,B1)&","&INDEX(A:A,C1) )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF( ROW()>COUNTA(A:A)*(COUNTA(A:A)-1)/2,"",IF(C1=COUNTA(A:A),B1+1,B1) )[/TD]

[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF( ROW()>COUNTA(A:A)*(COUNTA(A:A)-1)/2,"",IF(C1=COUNTA(A:A),B2+1,C1+1) )
[/TD]

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