Return cells(rows) that exist in 1 table but not the other ?

[zed369]

Good evening friends!

I have a what may be simple-for-some-but-for-the-life-of-me-I-just-can't-figure-it-out situation and in need of your help!

I have 2 sets of data in different sheets and I'd like to flag the differences between the two. I'd like to have the rows that exist in Data 2 but not in Data 1.

Data 1 and Data 2 sheets hold the two data sets and the 3rd sheet holds the results I want.

Even 1 tiny difference in any of the cells in a row should return the entire row.

The attached is just a mock-up I wrote and the actual data sets will have thousands of rows and about 9 or 10 columns(if this matters).

Thank you very much in advance!

[Pete_UK]

You could put this formula in F2 of Data_1 sheet, and copy it down to the bottom of your data:

=A2&B2&C2&D2

Put the same formula in F2 of Data_2 and copy that down. These formulae join the 4 fields of data into one cell. Then you can use this formula in cell G2:

=IF(ISNA(MATCH(F2,'Data 1'!F:F,0)),MAX(G$1:G1)+1,"ok")

Any numbers which occur down this column indicate those records which are not present in Data_1. Then you can use this formula in A2 of the Result sheet:

=IFERROR(INDEX('Data 2'!A:A,MATCH(ROWS($1:1),'Data 2'!$G:$G,0)),"")

Copy across to D2, then copy all 4 formulae down as far as you like (until you get blanks).

Hope this helps.

Pete

[zed369]

This works! Thank you!

[Pete_UK]

You're welcome - thanks for the rep.

Pete