Google sheets query - Cartesian join between two ranges

Question

How can we achieve the following join between two ranges on Google Spreadsheet using the query function?

Range 1

Model   Style
Nissan  Coupe
Nissan  Truck
Honda   Sedan
Honda   Hatchback
Toyata  Truck
Honda   Coupe

Range 2

Style       Engine
Coupe       1200 cc
Coupe       1500 cc
Sedan       1000 cc
Truck       2000 cc
Sedan       1800 cc
Hatchback   800 cc

Output

Model   Style       Engine
Nissan  Coupe       1200 cc
Nissan  Coupe       1500 cc
Honda   Sedan       1000 cc
Nissan  Truck       2000 cc
Honda   Sedan       1800 cc
Honda   Hatchback   800 cc
Honda   Coupe       1200 cc
Honda   Coupe       1500 cc
Toyata  Truck       2000 cc

Similar to this Q: https://stackoverflow.com/questions/42805885/generate-all-possible-combinations-for-columns-in-google-spreadsheets — Max Makhrov, Dec 11 '17 at 07:25

score 13 · Accepted Answer · 2017-12-11T03:18:45.857

The Data Visualization Language, which we use via the query command, does not support joins of any kind. The desired result can be obtained with a custom function.

Its format is =cartesianJoin(arr1, arr2, col1, col2) where the first two arguments are ranges to be joined, and the other two are column numbers (relative to the range) on which the join is to be made. For example, in your case the first array could be in A2:B8 and the second in D2:E8. Then the formula would be

=cartesianJoin(A2:B8, D2:E8, 2, 1)

indicating that we join by the second column of first array (B) being equal to the first column of the second array (D).

function cartesianJoin(arr1, arr2, col1, col2) {
  var output = [];
  for (var i = 0; i < arr1.length; i++) {
    var r1 = arr1[i];
    var matching = arr2.filter(function(r2) {
      return r1[col1 - 1] === r2[col2 - 1];
    }).map(function(r2) {
      var copyr2 = r2.slice(0);
      copyr2.splice(col2 - 1, 1);
      return r1.concat(copyr2);
    });
    output = output.concat(matching);
  }
  return output;                     
}

Logic: for each row of first array (r1), filter the second array by the equality requirement, then concatenate each of the matching rows to r1, first removing the matched column so it does not appear twice.

Screenshot:

sheet

Thanks I will attempt using the custom function route and vote once done — Ashish Dhandharia, Dec 10 '17 at 20:48
This did not give the desired output. For eg. There is no couple with 1000 cc engine but I got this in the output ==> Nissan Coupe 1200 cc Nissan Coupe 1500 cc Nissan Coupe 1000 cc Nissan Coupe 2000 cc Nissan Coupe 1800 cc Nissan Coupe 800 cc Nissan Truck 1200 cc Nissan Truck 1500 cc Nissan Truck 1000 cc Nissan Truck 2000 cc Nissan Truck 1800 cc Nissan Truck 800 cc — Ashish Dhandharia, Dec 11 '17 at 03:08
Works in my spreadsheet. I edited in a screenshot that shows the formula and its result. — , Dec 11 '17 at 03:19
Yes it works and incredibly efficient! Only if i could give this 20 upvotes! Thanks a bunch — Ashish Dhandharia, Dec 11 '17 at 03:32

Google sheets query - Cartesian join between two ranges

1 Answers1