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

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 compa
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!

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, EditPaste SpecialPaste: Formats)

Ola Sandström


Note:
Example zip-file:
http://www.excelforum.com/attachment...tid=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 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=385770