How do I find every combination of elements

user_6098534 0
Viewed 115

I have a list of elements:

John
Richard
Bill
Bob
Walter

And I would like to do something to get every possible combination of them in two columns excluding duplicates. Here is the example:

A            B
John         Richard
John         Bill
John         Bob
John         Walter
Richard      Bill
Richard      Bob
Richard      Walter
Bill         Bob
Bill         Walter
Bob          Walter

As you can see I excude John-John and also Walter-Bill because there already is a Bill-Walter combination. How do I do that using formulas?

array-formulas google-sheets-formula google-apps-script google-sheets javascript
3 Answers

Here is my approach:

function myFunction() {
var names = ['John', 'Richard', 'Bill', 'Bob', 'Walter'];
var result = []
var sheet = SpreadsheetApp.getActive().getSheetByName("sheet1")                        
names.forEach( (element, index) => {
  names.slice(index+1,names.length).forEach ( (rest) => {                                      
  result.push([element,rest])        
})})
sheet.getRange(2,1,result.length,result[0].length).setValues(result);
}

Output

Explanation:

Essentially you iterate through the names list. Starting from each element you only concatenate with the next elements. For example, John is the first element in the name list, you concatenate John with Richard, Bill, Bob and Walter. Then you go to the next element which is Richard and you concatenate him with Bill, Bob and Walter. In this way, you avoid having duplicates like: John - John or John - Richard and Richard - John. The forEach method helps you achieve this iterative approach.

References:

forEach()

user_112252910

How do I do that using formulas?

use:

=ARRAYFORMULA(SORT(UNIQUE(QUERY(IF(
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(A1:A)),  CHAR(9)))>
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A&CHAR(9),  COUNTA(A1:A))), CHAR(9))), {
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(A1:A)),  CHAR(9))), 
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A&CHAR(9),  COUNTA(A1:A))), CHAR(9)))}, {
 TRANSPOSE(SPLIT(CONCATENATE(REPT(A1:A&CHAR(9),  COUNTA(A1:A))), CHAR(9))), 
 TRANSPOSE(SPLIT(REPT(CONCATENATE(A1:A&CHAR(9)), COUNTA(A1:A)),  CHAR(9)))}), 
 "where Col1<>Col2", 0))))

enter image description here

user_56326290

If A1:A5 contained the names,

=ARRAYFORMULA(SPLIT(QUERY(
  FLATTEN(
    IF(SEQUENCE(COUNTA(A1:A5))<TRANSPOSE(SEQUENCE(COUNTA(A1:A5))),
      A1:A5& "☤" & TRANSPOSE(A1:A5)
      ,)
   ),
  "where Col1 is not null",0),"☤"))
  • SEQUENCE(5)<TRANSPOSE(SEQUENCE(5) to create a "true" upper triangular(with diagonal 0) 2D matrix
0   1   1   1   1
0   0   1   1   1
0   0   0   1   1
0   0   0   0   1
0   0   0   0   0
  • Translate the 2D matrix to CONCATEnated names using IF:
            John☤Rich   John☤Bill   John☤Bob    John☤Walt
                        Rich☤Bill   Rich☤Bob    Rich☤Walt
                                    Bill☤Bob    Bill☤Walt
                                                Bob☤Walt
  • FLATTEN the matrix, Remove empty spaces using QUERY and SPLIT the concatenated names back
user_84044530
Related