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
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI 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
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.
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.
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")
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