Duplicate data in multiple spreadsheets

[L]

I have 2 worksheets with rows of data. I need to compare the data in column
1, worksheet 1, to the data in column 1, worksheet 2. I need to find out if
there is any of the data in column 1, worksheet 1 that is duplicate to column
1, worksheet 2. Can someone assist with this formula?

 

[Pete_UK]

In a helper column of sheet 1 (eg in H1), put this formula:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"Not present","Duplicated")

then copy it down the full extent of the data in sheet 1. You can also
put this formula in the helper column of sheet 2:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"Not present","Duplicated")

and copy down. Now if you apply autofilter to the helper column and
select "Duplicated" from the filter pull-down, you will see all the
records that exist in both sheets. Alternatively, by selecting "Not
present" from the filter pull-down you will see those records which
are unique to that sheet.

Hope this helps.

Pete

 

[L]

Thanks Paul. I tried the formula, but when I pull it down, it responds as
all is Duplicated. Can you see anything wrong with my formula?

=IF(ISNA(MATCH(A$2:A$496,Sheet2!!A$2:A$2801,0)),"Not present","Duplicated".

I have 496 Rows in Sheet 1 and 2801 Rows in Sheet 2.

Thanks.
Linda

 

[L]

I tried it again...it works great! Thank you so much.

 

[Pete_UK]

I think you meant me, not Paul !!

Your formula should be:

=IF(ISNA(MATCH(A2,Sheet2!!A$2:A$2801,0)),"Not present","Duplicated")

You had missed a bracket off the end, and you want the first A2 to
change to A3, A4 etc as you copy down - you want to see if the cell on
that row matches ANY of the cells in Sheet2. If you used the full
column reference Sheet2!A:A, you would not have to concern yourself
with how many rows you had in each sheet.

Hope this helps.

Pete

 

[Pete_UK]

Our posts have crossed, but I'm happy that you got it to work. Thanks
for feeding back.

Pete