asked on

Compare two lists and highlight entire row on original list that does not exist on second list

Using excel I am trying to compare two lists and identify items do not exist in the second list.

I have a sheet with 10 (A-J) columns and ~10000 rows. I want the cells in row A1- A10000 to be compared to a single column list in sheet 2, A1-A200.
Then, if the data in any cell sheet 1, A1-A10000 does not match in sheet 2, A1-A200, the entire row (preferably) in sheet 1 (columns A-J) gets highlighted on sheet 1.
Then I can review and delete the row.
Example:
Sheet 1, rows 1-10000 has the truck # of 10000 trucks. Each truck # appears multiple times for the 10 different months. That's what makes up the ~10000 rows even though it's only ~1000 trucks . Columns A-J show the previous 10 months miles driven. However, some of the trucks have been disposed. The current list of trucks is in Sheet 2, A1-A200. I want excel to look at the list of current trucks in sheet 2, compare it to the list on sheet 1, and highlight entire row if that truck # is not Sheet 1.

I hope this makes sense.

Martin Liss

I interpreted your requirements as you wanting to have the "trucks" on sheet2 highlighted if they weren't on sheet1. I didn't know what was in the other columns of sheet2 so I highlighted the whole row but that can and should be changed.

See the results in this workbook that I dummied up as a result of running the 'FindDifferent' macro which probably should have been called 'FindMissing'.
29105854.xlsm

Madison Perkins

using conditional formatting I was able to highlight sheet1cell A if it exists in sheet2 cell A. not sure how to do the row but i am still looking at it.
conditional formatting > new rule > use formula.
=IFERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE),"")
trucks.xlsx

Madison Perkins

still not sure on highlighting row so I though together a SUMIF on sheet two that totals the miles in columns B-J if the truck number in sheet2 A exists in sheet 1A

=SUMIF(Sheet1!$A:$A,$A190,Sheet1!B:B)
trucks.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Keith Hunter

ASKER

Thanks all for the help. I tried each option and like the Conditional Formatting better. Also I was able to highlight all columns on the row by extending the selection to column J. Thanks!

Shums Faruk

Pleased to help!