Apologies for the re-post. Need to clarify this to avoid confusion. Is is
possible to compare two ranges and return a list of booleans illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first range
variable of the SUMPRODUCT, I want 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. The the first range in the
SUMPRODUCT function would look like this if you highlighted in and hit F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT function
to create the boolean data above?

Thanks

EM

I think you still need to clarify what you're trying to accomplish.

1) SUMPRODUCT requires its arguments to be of the same dimension. Yours
aren't.

2) Even if they were, the result would be zero, since Range2 consists
only of text.

You can generate the {1,0,0,0,0,1,0,0,0,0} using

COUNTIF(Range2,Range1)

For instance,

=SUMPRODUCT(COUNTIF(Range2,Range1))

returns 2. But since the arrays are different sizes

=SUMPRODUCT(COUNTIF(Range2,Range1),Range2)

returns #VALUE! (rather than zero).

In article ,
ExcelMonkey wrote:

Apologies for the re-post. Need to clarify this to avoid confusion. Is is
possible to compare two ranges and return a list of booleans illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first range
variable of the SUMPRODUCT, I want 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. The the first range in the
SUMPRODUCT function would look like this if you highlighted in and hit F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT function
to create the boolean data above?

Thanks

EM

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion. Is
is
possible to compare two ranges and return a list of booleans illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first range
variable of the SUMPRODUCT, I want 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. The the first range in
the
SUMPRODUCT function would look like this if you highlighted in and hit F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

It may be easier to send a spreadsheet example as explaining it seems to be
difficult. Do you have an email address that I can send an example to?

EM

"JE McGimpsey" wrote:

I think you still need to clarify what you're trying to accomplish.

1) SUMPRODUCT requires its arguments to be of the same dimension. Yours
aren't.

2) Even if they were, the result would be zero, since Range2 consists
only of text.

You can generate the {1,0,0,0,0,1,0,0,0,0} using

COUNTIF(Range2,Range1)

For instance,

=SUMPRODUCT(COUNTIF(Range2,Range1))

returns 2. But since the arrays are different sizes

=SUMPRODUCT(COUNTIF(Range2,Range1),Range2)

returns #VALUE! (rather than zero).

In article ,
ExcelMonkey wrote:

Apologies for the re-post. Need to clarify this to avoid confusion. Is is
possible to compare two ranges and return a list of booleans illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first range
variable of the SUMPRODUCT, I want 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. The the first range in the
SUMPRODUCT function would look like this if you highlighted in and hit F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT function
to create the boolean data above?

Thanks

EM

Yes this works but I just realized that this does not work they way I need it
too for duplicates. So if my example has a duplicate in Range 2 (i.e. "Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should. But I would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer boolean as
I originally suggested. How would I incorporate this to accomodate the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion. Is
is
possible to compare two ranges and return a list of booleans illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first range
variable of the SUMPRODUCT, I want 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. The the first range in
the
SUMPRODUCT function would look like this if you highlighted in and hit F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

Why don't you tell us what you are attempting to do with an example *and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way I need
it
too for duplicates. So if my example has a duplicate in Range 2 (i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should. But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer boolean
as
I originally suggested. How would I incorporate this to accomodate the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first
range
variable of the SUMPRODUCT, I want 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. The the first range
in
the
SUMPRODUCT function would look like this if you highlighted in and hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

Below, I have annual data ("profiles") in range B2:D6. I use the names in
range A2:A6 to drive data validation dropdown boxes in the range B9:D11.

My goal is to use this data to populate various business units of a firm
("sites") across various cost centres ("Var Cost"). In range B14:D16 I want
to show the consolidated "Site" data expressed across time for each "Var
Cost".

The solution you provided works fine if I do not have duplicates. Using
=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B9:$D9,0))),B$2:B$6) in cell B14, I
get the sum of Profiles1,2 and 3 for 2006. However this does not work in B15
as =SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B10:$D10,0))),B$2:B$6) has a
duplicate in it. The result does not take into account the duplicate
"Profile 2".

I was hoping that I could come up with a way to use the sumproduct formual
so that I could create an array which showed the number of times the chosen
profiles (in data validation boxes) per Var Cost showed up in the original
list of "Profiles" (B2:B6).

{1,1,1,0,0} for B14
{0,2,1,0,0} for B15
{0,0,1,1,1} for B16

Not I am doing this because I do not want to use pivot tables nor VBA. It
needs to be formulas. And I want to consolidate the "Site" data. Any easier
way around this would be to not consolidate the data and break "Var Cost"
down by site. But I do not want to do this.

Is there a way to compare two ranges where you can create an array
illustrating the number of times the items in the first array appear in the
second array? If not, do you have any other ideas?


A B C D
1 2006 2007 2008
2 Profile 1 0.50 0.65 0.60
3 Profile 2 0.68 0.17 0.37
4 Profile 3 0.51 0.42 0.94
5 Profile 4 0.50 0.08 0.07
6 Profile 5 0.87 0.14 0.19

7 Site 1 Site 2 Site 3
8
9 Var Cost 1 Profile 1 Profile 2 Profile 3
10 Var Cost 2 Profile 2 Profile 3 Profile 2
11 Var Cost 5 Profile 5 Profile 4 Profile 3
12
13 2006 2007 2008
14 Var Cost 1 1.696 ? ?
15 Var Cost 2 1.193 ?
16 Var Cost 3 ? ?

Thanks for your patience.

EM


"T. Valko" wrote:

Why don't you tell us what you are attempting to do with an example *and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way I need
it
too for duplicates. So if my example has a duplicate in Range 2 (i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should. But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer boolean
as
I originally suggested. How would I incorporate this to accomodate the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first
range
variable of the SUMPRODUCT, I want 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. The the first range
in
the
SUMPRODUCT function would look like this if you highlighted in and hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

Take a look at this screencap:

http://img477.imageshack.us/img477/1061/sampleja9.jpg

Biff

"ExcelMonkey" wrote in message
...
Below, I have annual data ("profiles") in range B2:D6. I use the names in
range A2:A6 to drive data validation dropdown boxes in the range B9:D11.

My goal is to use this data to populate various business units of a firm
("sites") across various cost centres ("Var Cost"). In range B14:D16 I
want
to show the consolidated "Site" data expressed across time for each "Var
Cost".

The solution you provided works fine if I do not have duplicates. Using
=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B9:$D9,0))),B$2:B$6) in cell B14,
I
get the sum of Profiles1,2 and 3 for 2006. However this does not work in
B15
as =SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B10:$D10,0))),B$2:B$6) has a
duplicate in it. The result does not take into account the duplicate
"Profile 2".

I was hoping that I could come up with a way to use the sumproduct formual
so that I could create an array which showed the number of times the
chosen
profiles (in data validation boxes) per Var Cost showed up in the original
list of "Profiles" (B2:B6).

{1,1,1,0,0} for B14
{0,2,1,0,0} for B15
{0,0,1,1,1} for B16

Not I am doing this because I do not want to use pivot tables nor VBA. It
needs to be formulas. And I want to consolidate the "Site" data. Any
easier
way around this would be to not consolidate the data and break "Var Cost"
down by site. But I do not want to do this.

Is there a way to compare two ranges where you can create an array
illustrating the number of times the items in the first array appear in
the
second array? If not, do you have any other ideas?


A B C D
1 2006 2007 2008
2 Profile 1 0.50 0.65 0.60
3 Profile 2 0.68 0.17 0.37
4 Profile 3 0.51 0.42 0.94
5 Profile 4 0.50 0.08 0.07
6 Profile 5 0.87 0.14 0.19

7 Site 1 Site 2 Site 3
8
9 Var Cost 1 Profile 1 Profile 2 Profile 3
10 Var Cost 2 Profile 2 Profile 3 Profile 2
11 Var Cost 5 Profile 5 Profile 4 Profile 3
12
13 2006 2007 2008
14 Var Cost 1 1.696 ? ?
15 Var Cost 2 1.193 ?
16 Var Cost 3 ? ?

Thanks for your patience.

EM


"T. Valko" wrote:

Why don't you tell us what you are attempting to do with an example *and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way I
need
it
too for duplicates. So if my example has a duplicate in Range 2 (i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should. But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer
boolean
as
I originally suggested. How would I incorporate this to accomodate the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first
range
variable of the SUMPRODUCT, I want 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. The the first
range
in
the
SUMPRODUCT function would look like this if you highlighted in and
hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

Slick! I was on the right track assuming a countif type function instead of
a Match. Just could not get it to work. Thanks a million. I really
appreciate it. Been plaguing me for a few days. I can sleep now!

EM

"T. Valko" wrote:

Take a look at this screencap:

http://img477.imageshack.us/img477/1061/sampleja9.jpg

Biff

"ExcelMonkey" wrote in message
...
Below, I have annual data ("profiles") in range B2:D6. I use the names in
range A2:A6 to drive data validation dropdown boxes in the range B9:D11.

My goal is to use this data to populate various business units of a firm
("sites") across various cost centres ("Var Cost"). In range B14:D16 I
want
to show the consolidated "Site" data expressed across time for each "Var
Cost".

The solution you provided works fine if I do not have duplicates. Using
=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B9:$D9,0))),B$2:B$6) in cell B14,
I
get the sum of Profiles1,2 and 3 for 2006. However this does not work in
B15
as =SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B10:$D10,0))),B$2:B$6) has a
duplicate in it. The result does not take into account the duplicate
"Profile 2".

I was hoping that I could come up with a way to use the sumproduct formual
so that I could create an array which showed the number of times the
chosen
profiles (in data validation boxes) per Var Cost showed up in the original
list of "Profiles" (B2:B6).

{1,1,1,0,0} for B14
{0,2,1,0,0} for B15
{0,0,1,1,1} for B16

Not I am doing this because I do not want to use pivot tables nor VBA. It
needs to be formulas. And I want to consolidate the "Site" data. Any
easier
way around this would be to not consolidate the data and break "Var Cost"
down by site. But I do not want to do this.

Is there a way to compare two ranges where you can create an array
illustrating the number of times the items in the first array appear in
the
second array? If not, do you have any other ideas?


A B C D
1 2006 2007 2008
2 Profile 1 0.50 0.65 0.60
3 Profile 2 0.68 0.17 0.37
4 Profile 3 0.51 0.42 0.94
5 Profile 4 0.50 0.08 0.07
6 Profile 5 0.87 0.14 0.19

7 Site 1 Site 2 Site 3
8
9 Var Cost 1 Profile 1 Profile 2 Profile 3
10 Var Cost 2 Profile 2 Profile 3 Profile 2
11 Var Cost 5 Profile 5 Profile 4 Profile 3
12
13 2006 2007 2008
14 Var Cost 1 1.696 ? ?
15 Var Cost 2 1.193 ?
16 Var Cost 3 ? ?

Thanks for your patience.

EM


"T. Valko" wrote:

Why don't you tell us what you are attempting to do with an example *and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way I
need
it
too for duplicates. So if my example has a duplicate in Range 2 (i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should. But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer
boolean
as
I originally suggested. How would I incorporate this to accomodate the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in message
...
Apologies for the re-post. Need to clarify this to avoid confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the first
range
variable of the SUMPRODUCT, I want 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. The the first
range
in
the
SUMPRODUCT function would look like this if you highlighted in and
hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this SUMPRODUCT
function
to create the boolean data above?

Thanks

EM

You're welcome!

Biff

"ExcelMonkey" wrote in message
...
Slick! I was on the right track assuming a countif type function instead
of
a Match. Just could not get it to work. Thanks a million. I really
appreciate it. Been plaguing me for a few days. I can sleep now!

EM

"T. Valko" wrote:

Take a look at this screencap:

http://img477.imageshack.us/img477/1061/sampleja9.jpg

Biff

"ExcelMonkey" wrote in message
...
Below, I have annual data ("profiles") in range B2:D6. I use the names
in
range A2:A6 to drive data validation dropdown boxes in the range
B9:D11.

My goal is to use this data to populate various business units of a
firm
("sites") across various cost centres ("Var Cost"). In range B14:D16 I
want
to show the consolidated "Site" data expressed across time for each
"Var
Cost".

The solution you provided works fine if I do not have duplicates.
Using
=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B9:$D9,0))),B$2:B$6) in cell
B14,
I
get the sum of Profiles1,2 and 3 for 2006. However this does not work
in
B15
as =SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B10:$D10,0))),B$2:B$6) has
a
duplicate in it. The result does not take into account the duplicate
"Profile 2".

I was hoping that I could come up with a way to use the sumproduct
formual
so that I could create an array which showed the number of times the
chosen
profiles (in data validation boxes) per Var Cost showed up in the
original
list of "Profiles" (B2:B6).

{1,1,1,0,0} for B14
{0,2,1,0,0} for B15
{0,0,1,1,1} for B16

Not I am doing this because I do not want to use pivot tables nor VBA.
It
needs to be formulas. And I want to consolidate the "Site" data. Any
easier
way around this would be to not consolidate the data and break "Var
Cost"
down by site. But I do not want to do this.

Is there a way to compare two ranges where you can create an array
illustrating the number of times the items in the first array appear in
the
second array? If not, do you have any other ideas?


A B C D
1 2006 2007 2008
2 Profile 1 0.50 0.65 0.60
3 Profile 2 0.68 0.17 0.37
4 Profile 3 0.51 0.42 0.94
5 Profile 4 0.50 0.08 0.07
6 Profile 5 0.87 0.14 0.19

7 Site 1 Site 2 Site 3
8
9 Var Cost 1 Profile 1 Profile 2 Profile 3
10 Var Cost 2 Profile 2 Profile 3 Profile 2
11 Var Cost 5 Profile 5 Profile 4 Profile 3
12
13 2006 2007 2008
14 Var Cost 1 1.696 ? ?
15 Var Cost 2 1.193 ?
16 Var Cost 3 ? ?

Thanks for your patience.

EM


"T. Valko" wrote:

Why don't you tell us what you are attempting to do with an example
*and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way
I
need
it
too for duplicates. So if my example has a duplicate in Range 2
(i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should.
But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer
boolean
as
I originally suggested. How would I incorporate this to accomodate
the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to
incorporate a
countif into the array.

Thanks

EM

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

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in
message
...
Apologies for the re-post. Need to clarify this to avoid
confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one 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 (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the
first
range
variable of the SUMPRODUCT, I want 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. The the first
range
in
the
SUMPRODUCT function would look like this if you highlighted in
and
hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this
SUMPRODUCT
function
to create the boolean data above?

Thanks

EM