Compare alpha and numeric values within a range

G

Greg

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
 
H

Harlan Grove

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.
Click to expand...

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)
 
G

Greg

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.
 
P

Peo Sjoblom

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
 
G

Greg

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
 
J

Jim Cone

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" <[email protected]>
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
 
P

Peo Sjoblom

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
 
J

Jim Cone

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" <peo.sjoblom@nw^^excelsolutions.com>
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
 
H

Harlan Grove

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".
Click to expand...
....

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).
 
J

Jim Cone

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 said:
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".
Click to expand...
....

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).
 
Top