VLOOKUP Function and color coding

[Kandavalli.Kiran]

Team,

I have two sheets EDW1, EDW2. Please find the same attached.

EDW1 has less records where as EDW2 has more records.

In Comparison Sheet, I need to compare EDW2 with EDW1 results (Basically the value is ADD)

Steps to follow

1) Group the duplicate values in EDW1 (Do not consider Order ID)
2) Group the duplicate values in EDW2 (Do not consider Order ID)
3) Now Compare EDW2 with EDW1 sheet
4) The matched records should be highlighted with Green
5) Extra records in EDW1 should be highlighted with RED
6) Extra records in EDW2 should be highlighted with Blue

Please do let me know if I am not clear

Thanks!
Kiran

[Rev12]

Hi,

A. I would like to suggest to Concatenate Names for you to have exact count of duplicate values

Amanda Johnson copy this formula in Cell F183 =CONCATENATE(C183," ",D183)
Amanda netzel

B. do Conditional Formatting for Concatenated Names for duplicated (Home>>Conditional Formatting>>Highlights Cells Rule>>Duplicate Values)
C. Sort by Color

Then let us know if you are near to what you want.

In Steps 3. What do you want to compare? You mean how mean are duplicated by name and by ID Nos.?

Thanks

Rev12

[FDibbins]

an alternative approach...
helper column on 1
F2=COUNTIFS($C$2:C2,C2,$D$2:D2,D2)
your profile says you have 2007, dont think that has countifS. If not, use this instead...
=SUMPRODUCT(--($C$2:C2=C2),--($D$2:D2=D2))
copy down as needed

Do the same for 2

This will give you a count of unique names (they will all show 1)

On Comp sheet...
also not sure how you want to handle that? Can you explain more and show some examples?