I am trying to compare data across multiple cell ranges to see if there are
any variances in the data. And the data in the cells can have various
formats, text, numbers, dates, etc. For example:

Cell(s) - Native Information
A1 - ABC
B1 - 123
C1 - 10/14/08

Cell(s) - Comparison Data
G1 - CDF
H1 - 123
I1 - 10/24/08

I am not really interested in knowing the exact cell that changed, just if
there was a change in comparison of the like ranges.

Hi,

You could use conditional formatting, but it would show you every cell that
did not match. If you only interested in know IF the two range match then

=SUMPRODUCT(--(C5:C7=G5:G7))

if the resulting number is equal to the number of cells in the range than
the ranges are the same.

Or you can modify this to read

=SUMPRODUCT(--(C5:C7=G5:G7))=COUNTA(C5:C7)

Then the answer will be TRUE if both ranges match and FALSE if they don't

If this helps, please click the Yes button

Cheers,
Shane DEvenshire

"Paul" wrote:

> I am trying to compare data across multiple cell ranges to see if there are
> any variances in the data. And the data in the cells can have various
> formats, text, numbers, dates, etc. For example:
>
> Cell(s) - Native Information
> A1 - ABC
> B1 - 123
> C1 - 10/14/08
>
> Cell(s) - Comparison Data
> G1 - CDF
> H1 - 123
> I1 - 10/24/08
>
> I am not really interested in knowing the exact cell that changed, just if
> there was a change in comparison of the like ranges.

Hi Shane,

I used your function =SUMPRODUCT(--(C5:C7=G5:G7)) and it located exact
entries about 80% of the time. I am comparing 7 data cells to another 7 data
cells in the same row, so I get a 7 if both ranges are all exact and 6 and
below means the number of matching cells. I get a lot of 6's but the data in
all 7 data cells are identical to the other 7 data cells.

Any other ideas?

Thank you,

Tommy

"Shane Devenshire" wrote:

> Hi,
>
> You could use conditional formatting, but it would show you every cell that
> did not match. If you only interested in know IF the two range match then
>
> =SUMPRODUCT(--(C5:C7=G5:G7))
>
> if the resulting number is equal to the number of cells in the range than
> the ranges are the same.
>
> Or you can modify this to read
>
> =SUMPRODUCT(--(C5:C7=G5:G7))=COUNTA(C5:C7)
>
> Then the answer will be TRUE if both ranges match and FALSE if they don't
>
> If this helps, please click the Yes button
>
> Cheers,
> Shane DEvenshire
>
> "Paul" wrote:
>
> > I am trying to compare data across multiple cell ranges to see if there are
> > any variances in the data. And the data in the cells can have various
> > formats, text, numbers, dates, etc. For example:
> >
> > Cell(s) - Native Information
> > A1 - ABC
> > B1 - 123
> > C1 - 10/14/08
> >
> > Cell(s) - Comparison Data
> > G1 - CDF
> > H1 - 123
> > I1 - 10/24/08
> >
> > I am not really interested in knowing the exact cell that changed, just if
> > there was a change in comparison of the like ranges.