Comparing Data Across Cell Ranges

[Paul]

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.

 

[Shane Devenshire]

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

 

[lilhoot]

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