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
LVL 1
EscanabaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Name the data on the MasterSheet 'ListData' and then use:
=VLOOKUP($B1,ListData,3,FALSE)=""
as the CF formula on the Data sheet.
See attached.
EE-Sample.xlsx
0
EscanabaAuthor Commented:
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?
0
Rory ArchibaldCommented:
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?
0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

EscanabaAuthor Commented:
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.
0
Rory ArchibaldCommented:
Just to check, is it the data sheet or the master sheet you want coloured?
0
EscanabaAuthor Commented:
The data sheet.  The one without the names should be colored.
0
Rory ArchibaldCommented:
OK, you need two named ranges on the master sheet - 'Codes' is the first column and 'Names' is the last name column. The CF formula then becomes:

=LOOKUP(2,1/((Codes=$B1)*(Names="")))=1

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaAuthor Commented:
Perfect!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.