12

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two.

Is there an easy way to do this?

Here's an example of what I want to achieve:

List 1  List 2  Expected result

 a       1        e
 b       2        f
 c       e      
 d       4      
 e       f      
 f       6      

Example Google Sheet

  • Can you share a doc with us and explain the expected result a bit? – Jacob Jan Tuinstra Feb 18 '13 at 19:14
16

There is no need to jump into GAS, standard spreadsheet functions handle this easily.

Please paste the following into a cell in your example

=iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match")

The match function generates a cartesian product of the two ranges full of errors except where a match is found. It returns an index into the 2nd range when a match is found.

The filter function throws away all the crap and returns only the correctly indexed values.

The iferror helps to get a tidy result if there are no matches at all.

The ranges can be of any length, as indicated by the $A$2:$A idiom.

3

This little script will compare two ranges, as a formula:

function COMPARE(array1, array2) {
  var array = [];  
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        array.push([array1[i][0]]);
      }
    }
  }
  return array;
}

In your sheet you can add in cell D2 the following formula :

=COMPARE(A2:A7,B2:B7)

Add the script via the tools menu, script editor.

  • I welcome plain old formula's. I could think of them immediately..... – Jacob Jan Tuinstra Feb 19 '13 at 12:36
  • 1
    could should/must be couldn't....... – Jacob Jan Tuinstra Feb 19 '13 at 12:48
  • 1
    Thanks, @Jacob! I never had a real reason to use formulas in gDocs - thanks for opening me up to this world! – NoamNelke Feb 19 '13 at 19:03
2

In case you are looking how to find a subtraction of two ranges (elements of range 1 that are not in range 2), like:

List 1  List 2  Expected result

 a       1        a
 b       2        b
 c       e        c
 d       4        d
 e       f      
 f       6    

Here is a formula for you:

=iferror(filter($A$2:$A, iserror(match($A$2:$A, B2:$B , false))), "no match")
-1

This little script will compare two ranges, as a formula:

function intersect(array1, array2) {
  var array = [];  
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        array.push([array1[i][0]]);
      }
    }
  }
  return array;
}

Extra:

function extersect(array1, array2) {
  var array = [];  
  var tmp = true;
  for(i=0; i<array1.length; i++) {
    for(j=0; j<array2.length; j++) {
      if(array1[i][0] == array2[j][0]) {
        // the extra square brackets will make it a 2D array, 
        // aligning it vertically
        tmp = false;
      }      
    }
    if(tmp == true) 
    {
      array.push([array1[i][0]]);
    }
    tmp = true;
  }
  return array;
}

In your sheet you can add in cell D2 the following formula :

=intersect(A2:A7,B2:B7)

Add the script via the tools menu, script editor.

=intersect(A2:A7,B2:B7) returns results that present on both arrays array1, array2.

=extersect(A2:A7,B2:B7) returns values of array1 that does not exists in range B2:B7

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.