Comparing two ranges and the positions of duplicates

[Guest]

Is is possible to compare two ranges and return a list of booleans
illustrating where the values in 1 range reside in the other.

Assume Range 1 is as follows (A1:A10):
Dog
Cat
Man
Women
Car
Rain
Black
In
Peter
Acid

Assume Range 2 is as follows (B2:B6):
Rain
Blue
Mary
Stick
Dog

I want the result to illustrate where the items in Range 2 reside in Range 1
as follows. {1,0,0,0,0,1,0,0,0,0} showing that both "Rain" and "Dog" reside
in Range 1 in positions 1 and 6.

Any ideas?

Thanks

EM

 

[Roger Govier]

Hi

In several stages, yes.
In C2 enter
=MATCH(B2,$A$1:$A$10,0)
and copy down through C3:C6
in D1
=IF(ISNUMBER(VLOOKUP(A1,$B$2:$C$6,2,0)),1,0)
and copy down through D210
This will give a vertical array on 1's and 0's in D110 which you could
then concatenate if required.

 

[Roger Govier]

Hi

If you used
=SUMPRODUCT(D110,E1:E10)
where E1:E10 was your other range, then it would work fine