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

 

[Tanjamuse]

Thanks

 

[Tanjamuse]

I would like to combine four or maybe five but I need a bit of twist if it's possible. The twist is that I would like to do only unique combinations like I've tried to show above. I don't know if this is possible, but I'm hoping someone in here can help me create the macro or tell me that it's impossible.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Antelope
[/TD]
[TD]Antelope,Bear,Chicken,Dog,Elephant
[/TD]
[/TR]
[TR]
[TD]Bear
[/TD]
[TD]Antelope,Chicken
[/TD]
[/TR]
[TR]
[TD]Chicken
[/TD]
[TD]Antelope,Dog
[/TD]
[/TR]
[TR]
[TD]Dog
[/TD]
[TD]Antelope,Elephant
[/TD]
[/TR]
[TR]
[TD]Elephant
[/TD]
[TD]Antelope,Frog
[/TD]
[/TR]
[TR]
[TD]Frog
[/TD]
[TD]Bear,Dog
[/TD]
[/TR]
</tbody>[/TABLE]

I hope I've explained it well.