excel - How to create all possible pair combinations without duplicates in Google Sheets?

Question

How to perform iteration over excel/google sheets cells to get pairwise combinations?

"string1"

"string2"
"string3"
...
"string10"

I'm looking at writing a function that can iterate over these strings to create the following:

"string1, string2" 
"string1, string 3" 
...
"string 1, string 10" 
"string 2, string 3" 
...
"string 2, string 10" 
"string3, string 4" 
... ... 
"string9 string10".

Is this possible in google sheets?

Answer 1

google-sheets

It is a hard task for native functions. Try a script and use it as a custom function:

function getTournament(teams_from_range)

    {
      // teams_from_range -- 2D Array  
      var teams = [];
      // convert to list
      teams_from_range.forEach(function(row) { row.forEach(function(cell) { teams.push(cell); } ); } );
      return getTournament_(teams);
    }
    
    
    function getTournament_(teams)
    {
      var start = 0;
      var l = teams.length;
      var result = [], game = [];
      
      // loop each value
      for (var i = 0; i < l; i++)
      {
        // loop each value minus current
        start++;
        for (var ii = start; ii < l; ii++)
        {
          game = []
          game.push(teams[i]);
          game.push(teams[ii]);  
          result.push(game);
        }  
      }
      
      return result;
    
    }

Usage:

=getTournament(A1:A10)