Compare alpha and numeric values within a range
 

Hi- First time poster here-

I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

Any assistance here would be greatly appreciated.

Thanks in advance

Compare alpha and numeric values within a range
 

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

Unfortuantely that formula doesn't seem to work on alpha cells, although your
"Index" suggestion might be something that I can use to work my way towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

My apologies- you are absolutely correct. I just redid the formula found a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.

Sincerely,
Greg

"Peo Sjoblom" wrote:

Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg"
wrote in message
...
My apologies- you are absolutely correct. I just redid the formula found a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.
Sincerely,
Greg

"Peo Sjoblom" wrote:
Using sumproduct this way has nothing to do with sumproduct as portrayed in
the help, Harlan's formula will simply check that only Joe is in this range
and not Joe and Mary and it will return TRUE if only one unique value exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells, although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is
unacceptable

I want a formula or function that will tell me this wihout me having to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

This works fine for me

=SUMPRODUCT(--(F13:J13=INDEX(F13:J13,1,1)))=COUNTA(F13:J13)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Jim Cone" wrote in message
...
Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg"
wrote in message
...
My apologies- you are absolutely correct. I just redid the formula found
a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.
Sincerely,
Greg

"Peo Sjoblom" wrote:
Using sumproduct this way has nothing to do with sumproduct as portrayed
in
the help, Harlan's formula will simply check that only Joe is in this
range
and not Joe and Mary and it will return TRUE if only one unique value
exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells,
although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result
is
unacceptable

I want a formula or function that will tell me this wihout me having
to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to
see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

Peo,
Yes it does and so does this...
=SUMPRODUCT(--(F13:J13=INDEX(F13:J13,0,1)))=COUNTA(F13:J13)
but both versions return True with...
0 0 blank 5 0

I think I'll go back to VBA <g

Regards,
Jim Cone


"Peo Sjoblom"
wrote in message
This works fine for me
=SUMPRODUCT(--(F13:J13=INDEX(F13:J13,1,1)))=COUNTA(F13:J13)
--
Regards,
Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Jim Cone" wrote in message
...
Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Greg"
wrote in message
...
My apologies- you are absolutely correct. I just redid the formula found
a
problem with my sytax. Once I corrected it, the formula worked perfectly.
Thank you boh for your help.
Sincerely,
Greg

"Peo Sjoblom" wrote:
Using sumproduct this way has nothing to do with sumproduct as portrayed
in
the help, Harlan's formula will simply check that only Joe is in this
range
and not Joe and Mary and it will return TRUE if only one unique value
exist
or FALSE if not

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Greg" wrote in message
...
Unfortuantely that formula doesn't seem to work on alpha cells,
although
your
"Index" suggestion might be something that I can use to work my way
towards a
solution. Thanks.


"Harlan Grove" wrote:

Greg wrote...
I am trying to figure out a formula that will allow me to compare the
contents of cells within a range and let me know if there is a value
that is
not the same as the others. For example:
Columns a,b,c,d,e and f and say "Joe"- this result is acceptable
Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result
is
unacceptable

I want a formula or function that will tell me this wihout me having
to
visually scan the data to determine this.

If you mean you want to check some range, which I'll denote rng, to
see
whether all cells in that range contain the same value, you could try

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

Compare alpha and numeric values within a range
 

Jim Cone wrote...
Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
....

First your formula. F13:J13 is a 1D range, 1 row by 5 columns.
INDEX(F13:J13,1,0) is also a 1D range consisting of the 1st row and all
columns of F13:J13, which is identical to F13:J13. So your
(F13:J13)=INDEX(F13:J13,1,0) compares F13:J13 to itself. I'd be very
surprised if this didn't result in a 1D array of 5 TRUEs. So your
SUMPRODUCT call is equivalent to the simpler COLUMNS(F13:J13), and your
formula as a whole is equivalent to the array formula
=COLUMNS(F13:J13)=COUNTA(F13:J13).

My formula,

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

compares every cell in rng against the first cell in rng, which is
always given by INDEX(rng,1,1) even if rng is multiple area. If all
cells in rng are the same (and nonblank), this will equal the number of
cells in rng. The COUNTA call will equal the number of nonblank cells
in rng. If all cells in rng evaluate to the same thing and are
nonblank, the SUMPRODUCT call will equal the COUNTA call.

Another way to have done this would have been

=SUMPRODUCT((1-ISBLANK(rng))/COUNTIF(rng,rng&""))=1

but it's longer and involves an O(N^2) COUNTIF call. Then again, it'd
be shorter still to use the array formula

=VAR(MATCH(rng,rng,0))=0

but the MATCH call is also O(N^2).

Compare alpha and numeric values within a range
 

Harlan,

Thanks for the explanation...
"compares every cell in rng against the first cell in rng"
is what I didn't grasp.
But as I said before, I think I will go back to VBA.<g

Regards,
Jim Cone


"Harlan Grove" wrote in message oups.com...
Jim Cone wrote...
Well, I had to make a couple of small changes to get it to work using
a range address...

=SUMPRODUCT(--((F13:J13)=INDEX(F13:J13,1,0)))=COUNTA(F13:J13)

I still don't understand how it could work using "1, 1" instead of "1, 0".
....

First your formula. F13:J13 is a 1D range, 1 row by 5 columns.
INDEX(F13:J13,1,0) is also a 1D range consisting of the 1st row and all
columns of F13:J13, which is identical to F13:J13. So your
(F13:J13)=INDEX(F13:J13,1,0) compares F13:J13 to itself. I'd be very
surprised if this didn't result in a 1D array of 5 TRUEs. So your
SUMPRODUCT call is equivalent to the simpler COLUMNS(F13:J13), and your
formula as a whole is equivalent to the array formula
=COLUMNS(F13:J13)=COUNTA(F13:J13).

My formula,

=sumproduct(--(rng=INDEX(rng,1,1))=COUNTA(rng)

compares every cell in rng against the first cell in rng, which is
always given by INDEX(rng,1,1) even if rng is multiple area. If all
cells in rng are the same (and nonblank), this will equal the number of
cells in rng. The COUNTA call will equal the number of nonblank cells
in rng. If all cells in rng evaluate to the same thing and are
nonblank, the SUMPRODUCT call will equal the COUNTA call.

Another way to have done this would have been

=SUMPRODUCT((1-ISBLANK(rng))/COUNTIF(rng,rng&""))=1

but it's longer and involves an O(N^2) COUNTIF call. Then again, it'd
be shorter still to use the array formula

=VAR(MATCH(rng,rng,0))=0

but the MATCH call is also O(N^2).