highlight cells in 2 columns that don't match cells in 2 othercolumns

  • Thread starter Thread starter ash.gary
  • Start date Start date
A

ash.gary

Hello,

I need to update my department's building and room # spreadsheet to
match another department's building and room # spreadsheet. The other
department's data is considered the correct one. I've merged the data
from both spreadsheets into one containing four columns. I would like
compare my data in columns 1 & 2 (shown below) to the correct data in
columns 3 & 4 and highlight cells in columns 1 & 2 that don't match
any of the cells in columns 3 & 4. Using the example below, two sets
of cells should be highlighted in columns 1 & 2 because they don't
appear in columns 3 & 4. Those cells are Building # 1 Room # 20 and
Building # 2 Room # 50.

|-----old data-----------------| |-------good
data------------|
Column 1 Column 2 Column 3 Column 4
Building # Room # Building # Room #
1 20 1 21
1 30 1
30
2 10 2 10
2 50 2 60

Does anyone know how I can do this?

Thanks!

Gary
 
Hi Gary,

Set up a third column in the Bldg2 list as an index, joining the bldg
& room:

=C2&D2

to get a number like "121" or "230".

Then in the first list, select all data then use Conditional
Formatting. Choose the "Formula is" option, then enter:

=ISERROR(MATCH(A2&B2,$E$2:$E$65536)

adjusting cell references as necessary. Choose a highlighting format
and you're done.

Regards,
 
Back
Top