Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated comparison and highlighting question
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! -- E. Aimee Bauer, PMP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated question, for me anyway! :) | Excel Worksheet Functions | |||
Complicated Function Question | Excel Discussion (Misc queries) | |||
Complicated question | Excel Discussion (Misc queries) | |||
Complicated Question | Excel Worksheet Functions | |||
Complicated question... | Excel Worksheet Functions |