Comparing Rows in Two Different Sheets

**eg6791** · 02-25-2014, 09:39 AM

Hello,

I have two sheets of similar data in a workbook. The data is formatted into rows with data in each row separated into 10 columns. The rows are jumbled in sheet 2 and are user inputted. What I want to do is to take a row from sheet 2 which should match a row exactly in sheet 1 and format that row in sheet 2 green if it has an exact match in sheet 1. Is there a formula in Excel that allows me to do this. I would do Conditional Formatting, but I dont believe it can take an entire row as an entry. Any help or guidance on this would be great.

Thank you for your time.

EG6791

**Richard Buttrey** · 02-25-2014, 09:47 AM

Perhaps the simplest way is to create two helper columns, one in each sheet and use them to concatenate the other 10 cells on a row. e.g. =A1&B1&C1...J1

Then in sheet 2, and assuming the helper column in both sheets is say K, in L1 enter

Formula:

=MATCH(K1,Sheet1!K:K,False)

Drag this down your whole list and then filter for the #N/A values which indicate that no match has been found. Then select the filtered rows and format the background colour as appropriate.