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

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

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

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