Get unique combinations using array formula?

[shayej]

Hi I have seen to many combination solutions but none of them uses array formula. Is it possible to generate unique combination (order doesn’t matter) using array formula.


Column A has list of participant, column C should give all possible unique combination.

This is what I want:

1	Amy		Amy John
3	John		Amy Charlie
4	Charlie		Amy Ian
5	Ian		John Charlie
6			John Ian
7			Charlie Ian

<tbody>
[TD="align: center"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]

</tbody>

I am trying to use Google sheet, therefore I cannot use vba or macro.

Any help will be much appreciated.

Thank you.

 

[shayej]

I found this formula which gives me what I want but it requires 2 column of list. I only have I column of list.

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

 

[VBA Geek]

not a big fan of long formulas but try:

Given your layout A1:A4

Try in D2 and drag down

=INDEX($A$1:$A$4,SMALL(IF(ROW($A$1:$A$4)<=TRANSPOSE(ROWS($A$1:$A$4)-ROW($A$1:$A$4)),ROW($A$1:$A$4)),ROWS(D$2:D2))) & " " & INDEX($A$1:$A$4,SMALL(IF(ROW($A$1:$A$4)<=TRANSPOSE(ROWS($A$1:$A$4)-ROW($A$1:$A$4)),ROW($A$1:$A$4)),ROWS(D$2:D2))+1+INDEX(COUNTIF(D$1:D1,$A$1:$A$4&"*"),SMALL(IF(ROW($A$1:$A$4)<=TRANSPOSE(ROWS($A$1:$A$4)-ROW($A$1:$A$4)),ROW($A$1:$A$4)),ROWS(D$2:D2))))

confirm with Control Shift Enter

 

[shayej]

I get output as 0 (zero) for some reason.

 

[VBA Geek]

Is your data in A1:A4 and are you copying the whole formula and pasting it in D2 and entering it with CSE?

 

[shayej]

Is your data in A1:A4 and are you copying the whole formula and pasting it in D2 and entering it with CSE?

sorry pasted on D1 instead of D2. it worked like magic