Excel 2007 Comparison Solution Needed
Hello,
Please see attached file. I need a method of comparing the two worksheet tabs based on the following criteria:
If the data tab has an E Code that matches an E Code on the Master tab but there is no last name, highlight that row in columns A & B only in red.
Therefore, based on the attached example, row 4 has a matching E code on both tabs but there is no last name, so that row should be highlighted red. Rows 2 and 3 have matching E Codes but there are also names associated with those codes so no action required. Row 5 is missing a last name but the E Code does not match up between the two tabs, so no action required.
I am assuming someone can put this into a more compact formula with conditional formatting then the long-winded alternative I'm coming up with so any assistance would be greatly appreciated.
Thanks
EE-Sample.xlsx
Please see attached file. I need a method of comparing the two worksheet tabs based on the following criteria:
If the data tab has an E Code that matches an E Code on the Master tab but there is no last name, highlight that row in columns A & B only in red.
Therefore, based on the attached example, row 4 has a matching E code on both tabs but there is no last name, so that row should be highlighted red. Rows 2 and 3 have matching E Codes but there are also names associated with those codes so no action required. Row 5 is missing a last name but the E Code does not match up between the two tabs, so no action required.
I am assuming someone can put this into a more compact formula with conditional formatting then the long-winded alternative I'm coming up with so any assistance would be greatly appreciated.
Thanks
EE-Sample.xlsx
ASKER
Rorya - The actual dataset that I'm using is quite large so I didnt realize this until now. There are some E Codes that appear to repeat on the master tab. Will this present a problem in using your solution?
Depends. Can you have the same code for multiple names? How do you want the check to work if a given code has some names filled in and some blank?
ASKER
Just reviewed the data and yes, there can be the same code for different names. Is there a VBA solution where it can first compare to make sure the E Code matches on both tabs and if so, confirm if a last name exists on that row in column C? If there is a code match with no text in column C then highlight the row and then just loop it through the rest of the rows on the worksheet?
Basically what I'm trying to do is find a way to compare the two tabs to identify any coded territories that does not have a sales rep assigned to it.
Basically what I'm trying to do is find a way to compare the two tabs to identify any coded territories that does not have a sales rep assigned to it.
Just to check, is it the data sheet or the master sheet you want coloured?
ASKER
The data sheet. The one without the names should be colored.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Thank you!
=VLOOKUP($B1,ListData,3,FA
as the CF formula on the Data sheet.
See attached.
EE-Sample.xlsx