Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

Originally Posted by Tony Valko

If the 2 lists are in 1 dimensional arrays (single rows or single columns) then I can get the 3rd list using formulas. Just need to know the locations of the 2 lists and the exact destination for the 3rd list.

I think I have it figured out using these steps...

In the sheet OLD_DATA, type CalcCriteria in cell N1 and the following formula in cell N2 (or in two empty adjacent cells):

N2-> =COUNTIFS(NEW_DATA!$C$2:$C$579,$C2)=0

When the result is TRUE means that there is no find record in the sheet NEW_DATA.

Now, in the sheet OLD_DATA, use Conditional Formatting with the formula =COUNTIFS(NEW_DATA!$C$2:$C$579,$C2)=0 to highlighted the 332 items.

In the sheet OMITTED_DATA (source sheet), select a empty cell (A1, for example), have a click in Advanced, in the Sort & Filter group of the Data tab.


In the dialog (Advanced Filter), do the following:

Select the option Copy to Another Location

In the List Range box, type OLD_DATA!$A$1:$L$911

In the Criteria Range box, type OLD_DATA!$N$1:$N$2

In the Copy To box, type OMITTED_DATA!A1


Finally, press OK.