Complicated comparison and highlighting question

A

Aimee

I greatly apologize if this is already answered, but I did not find it...
Thank you in advance.

My Current Data:
Sheet 1 (old data):
A B
1 ID1 Text
2 ID2 Text [DELETED when compared to sheet 2]

Sheet 2 (new data):
A B
1 ID1 CHANGEDText
2 ID3 Text [NEW]

What I can do:
1) Find ID1 from Sheet 1 in Sheet 2 and compare text to determine changed or
matched. (Can do using vlookup/if combo also use conditional formatting to
highlight cells for "change" color)
2) Find ID2 from sheet 1 and determine missing from sheet 2 (Can do using
vlookup/if combo also use conditional formatting to highlight cells for
"deleted" color)
3) Find ID3 from sheet 2 and determine missing from sheet 1 (Can do using
vlookup/if combo also use conditional formatting to highlight cells for "new"
color)
4) Summary data to indicate what is new, changed and deleted.

Here is what I do not know how to do:
A) Do all this using one macro
B) Create a summary sheet that gives me counts like:
Rev 1.0 Rev 2.0
Changed 169 169
Deleted 111 0
New 0 268
Match 36 36
316 473

C) Be able to HIGHLIGHT just the changed text (or bold it)... [This would be
a VERY nice to have, but not absolutely necessary]

I would like to learn how to make:
Sheet 2 (newer data):
A B
1 ID1 CHANGEDText where "CHANGED" is bolded or highlighted
2 ID3 Text (and have this automatically highlighted with a "NEW" color)

Sheet 1 (older data):
A B
1 ID1 Text (and have this automatically highlighted with a "CHANGED" color)
2 ID2 Text (and have this automatically highlighted with a "DELETED" color)

Use the assumption that sheet 1 is an earlier version of the data and sheet
2 will superceded the data. All the rest of the info is for comparison.

There is more, but the individual text highlighting and the vlookups, ifs
and counts using VBA.

Most complicated Formula used is "=IF(ISERROR(VLOOKUP(A2,'Rev
1.0'!A:B,2,FALSE)),"NEW",IF(VLOOKUP(A2,'Rev
1.0'!A:B,2,FALSE)=B2,"MATCH","CHANGED"))". {and the equivalent in Rev 2.0
sheet}

This is an awful lot, but I think that if I can get the start of this, I can
reuse this over and over again for future projects!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top