Highlighting unique values amongst 2 excel sheets using conditional formatting.

New Contributor

I ran 2 reports days apart which has mostly the same info except one has 709 rows and the other has 679 rows. I'm trying to determine which rows are missing from the smaller report. I was going to look it over one by one, but there has to be a way to highlight the unique ones quicker. I have the 2 reports on the same workbook and was using "=$A:$H<>'Dom''s'!$A:$H" in conditional formatting to highlight the 30+ differences but I don't think it worked. Anyone know of a formula that will give me the unique rows, despite 90% of both sheets having the same info?

4 Replies

@DomVee 

Do the rows have a unique identifier? If so, in which column?

Columns A, C, & F are the columns that could have varying data between both sheets that I'm looking for. It's like a combination of data.

@DomVee 

Let's say the data begin in row 2, with headers in row 1.

On the first sheet, select the range you want to highlight if there is no match on the other sheet. The active cell in the selection should be in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=ISERROR(MATCH(1,('Dom''s'!$A$2:$A$680=$A2)*('Dom''s'!$C$2:$C$680=$C2)*('Dom''s'!$F$2:$F$680=$F2),0))

 

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

I have attached a small demo.