Comparing two ranges and the positions of duplicates
Hi
If you used
=SUMPRODUCT(D1:D10,E1:E10)
where E1:E10 was your other range, then it would work fine
--
Regards
Roger Govier
"ExcelMonkey" wrote in message
...
I see your point here. However, I want to be able to show the
{1,0,0,0,0,1,0,0,0,0} in one cell not a range of cells. Your example
shows
this across D1:D10.
The reason I am doing this is I want to then put the array of booleans
into
a SUMPRODUCT function like below:
SUMPRODUCT({1,0,0,0,0,1,0,0,0,0} , OtherRange)
As such, I want to be able to highlight this function in the cell and
hit F9
and see the {1,0,0,0,0,1,0,0,0,0} in the first part of my SUMPRODUCT
function.
Is there a way to do this?
Thanks
EM
"Roger Govier" wrote:
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 D2:D10
This will give a vertical array on 1's and 0's in D1:D10 which you
could
then concatenate if required.
--
Regards
Roger Govier
"ExcelMonkey" wrote in
message
...
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
|