Excel 2007 Comparion Assist

Hello,

Need assistance on a comparison report.  Please see attached.  I need a formula or VB that will look at the two tabs and if there is a matching E Code, confirm that E Code has a last name in the Master tab.  If there is no match, highlight the row to column F green on the master tab.

For example, on the sample provided, row 4 has a matching E code on the Data tab but there is no last name.  Therefore, that row would be highlighted green.  Rows 2 and 3 have matching E Codes but there are last names so those would not be highlighted.  Row 5 has a name but the E Code does not match the E code on the data tab so that would not be highlighted.

Thanks!
EE-Sample.xlsx
LVL 1
EscanabaManager - HR AnalyticsAsked:
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.

barry houdiniCommented:
You can do that with conditional formatting.

Firstly name the E_code column in Data sheet, naming it E-Code

Now in the master sheet select all the data or rows that might be filled - I selected A2:F20 - and apply this formula in conditional formatting

=AND(COUNTIF(E_Code,$A2)>0,$C2="")

That assumes start row at 2, change as required, see attached

regards, barry

26927459.xlsx

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
EscanabaManager - HR AnalyticsAuthor Commented:
So simple a caveman could do it.  :-)
Thank, Barry!  Exactly what I was hoping to see.
Saqib Husain, SyedEngineerCommented:
Was that a compliment?

:-=
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

EscanabaManager - HR AnalyticsAuthor Commented:
Actually yes it was indicating that it was a simple and effective solution to the problem being presented followed up by the THANKS.
EscanabaManager - HR AnalyticsAuthor Commented:
Of course, in this day and age to be completely politically correct if you are, indeed, a caveman ssaqibh, then I apologize for my prejudice remark.  Can only imagine what you poor people have been through with all of the insurance commercials these days.  :-)
Saqib Husain, SyedEngineerCommented:
Yeah! what worried me was that you were comparing our dear and respected genius Barry with us poor cavemen. Barry, Just ignore that comment, he did not mean it.
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.