Combine all possible combinations from single column without vba

[andrew_milonavic]

Hi Everyone,

I'd like to combine all possible combinations (text) from a single column into another.

I was able to find this 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))

which works perfectly for two columns of data but I wasn't able to make it work for just one column of data.

Hope you guys can help!

Thanks

Andrew

 

[wideboydixon]

Hi Andrew,

I'm not sure what you mean. Can you give an example?

WBD

 

[andrew_milonavic]

Hey Wideboydixon,

Thanks for the reply.

Lets say I've got 6 fruits (Apple, Orange, Grapefruit, Banana, Kiwi, Grapes) in column 1 (A1-A6). I'd like to excel to automatically combine all the possible combinations into column B. The data would then look like Apple orange, Apple grapefruit, apple banana etc etc.

The formula I posted does the possible combinations but needs to rows of data.

Make sense?

Thanks

Andrew

 

[wideboydixon]

Do you mean pick all possible pairs? Are you allowed to pick duplicates? {Apple, Apple}? Do you want to include triples also? {Apple, Orange, Grapefruit}. Quads?

WBD

 

[andrew_milonavic]

Hey Wideboydixon,

All possible pairs, no duplicates.

Thanks

Andrew

 

[wideboydixon]

Tricky and reasonably complex.

Book1
	A	B
1	Antelope	Antelope,Bear
2	Bear	Antelope,Chicken
3	Chicken	Antelope,Dog
4	Dog	Antelope,Elephant
5	Elephant	Antelope,Frog
6	Frog	Bear,Antelope
7		Bear,Chicken
8		Bear,Dog
9		Bear,Elephant
10		Bear,Frog
11		Chicken,Antelope
12		Chicken,Bear
13		Chicken,Dog
14		Chicken,Elephant
15		Chicken,Frog
16		Dog,Antelope
17		Dog,Bear
18		Dog,Chicken
19		Dog,Elephant
20		Dog,Frog
21		Elephant,Antelope
22		Elephant,Bear
23		Elephant,Chicken
24		Elephant,Dog
25		Elephant,Frog
26		Frog,Antelope
27		Frog,Bear
28		Frog,Chicken
29		Frog,Dog
30		Frog,Elephant
Sheet1

Cell Formulas
Range	Formula
B1	=IF(ROW()>COUNTA($A:$A)*(COUNTA($A:$A)-1),"",INDEX($A:$A,INT((ROW()-1)/(COUNTA($A:$A)-1))+1) & "," & INDEX($A:$A,MOD(ROW()-1,COUNTA($A:$A)-1)+1+IF(MOD(ROW()-1,COUNTA($A:$A)-1)>=INT((ROW()-1)/(COUNTA($A:$A)-1)),1,0)))

WBD

 

[andrew_milonavic]

That's awesome!!!! Thanks Wideboydixon

Greatly appreciated.

 

[andrew_milonavic]

Hey wideboydixon,

Is there a way to stop the formula from producing duplicates of the reverse order? Sticking with your example allowing "antelope, bear" but not "bear, antelope"??

Maybe that's what you were asking about previously, re duplicates, if so I appologize for not picking up on it.

Thanks

Andrew

 

[grub666]

You can just remove duplicates as an option in the excel or in a macro

 

[wideboydixon]

Trying to think about doing that in a formula gives me a headache. Trivial task for VBA though:

Public Sub GetUniquePairs()

Dim lastRow As Long
Dim thisRow As Long
Dim i As Long
Dim j As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
thisRow = 1
For i = 1 To lastRow - 1
    For j = i + 1 To lastRow
        Cells(thisRow, 2).Value = Cells(i, 1).Value & "," & Cells(j, 1).Value
        thisRow = thisRow + 1
    Next j
Next i

End Sub

WBD