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

2 Answers 2

up vote 3 down vote accepted

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.