• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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
0
Escanaba
Asked:
Escanaba
  • 4
  • 4
1 Solution
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
EscanabaAuthor Commented:
Perfect!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now