Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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: intersection of two sets

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

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
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
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;
}


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;
}
share|improve this answer
    
Raw code without any explanation isn't very helpful. –  Al E. Jan 15 at 14:22
    
intersect return results that present on both arrays array1, array2. But function extersect return values of array1 that does not exists in array2 –  GlobeCore.com Feb 4 at 14:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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