How to create all possible pair combinations without duplicates in 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)

Leave a Comment