How do I compare 2 sets of data and highlight differences?

[Perplexed1]

How do I compare two sets of data and highlight the differences if:
-Each set of data has three columns and I am comparing column A in data set
1 to column A in data set 2; column B in data set 1 to column B in data set 2
and so on...
-One set of data is properly formatted and the other set of data is in all
CAPS.
-One set of data may have rows ofrepeated data that should be consolidated
into one row before being compared

Here are 2 sample data sets to compare:
Data set 1:
D1_ColumnA D1_ColumnB D1_ColumnC
Cat United States Black
Dog United States Brown
Mouse Canada Grey
Fish Japan Orange
Bird Equador Green

D2_COLUMNA D2_COLUMNB D2_COLUMNC
CAT UNITED STATES BLACK
DOG CANADA BROWN
DOG CANADA BROWN
MOUSE NETHERLANDS GREY
FISH CHINA SILVER
BIRD EQUADOR GREEN

Thank you!

 

[olasa]

Does this help?

a) Use Conditional Formatting for non matching sets
b) Use Sumproduct(...) for duplicate rows in a Set*

If a duplicate row is deleted, the Conditional Formatting must updated
(Copy, Edit>Paste Special>Paste: Formats)

Ola Sandström


Note:
Example zip-file:
http://www.excelforum.com/attachment.php?attachmentid=3583&stc=1
* =COUNTIF(...,...)>1 array entered (Ctrl+Shift+Enter) can also be used


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3583 |
+-------------------------------------------------------------------+