Comparing data between 2 sheets to find duplicates

Hi,

I have 2 spreadsheets, one with our current database of about 45000 names and emails and another of about 250000 names and emails. I need to compare the 2 sheets to find any duplicate emails that are on the database sheet and on the 250000 sheet.

Once I find the emails on the 250000 sheet that are already in our database, I need to remove them from the 250000 sheet.

Is there a quick and easy way to do this?

Any help would be greatly appreciated.

Thanks so much!

It's doing exactly the same thing as your example, effectively.

Just to clarify, the formula gave above should be entered into the first relevant row of the workbook you're looking to delete rows from, the 250000 row of data one I presume from the original post.

It will check each row against the "database" workbook and if it finds the relevant email address in that file it will show how many times it appears.

If zero, it does not appear, if 1 it appears once, if 2 it appears twice on the database workbook etc...

Thanks! I am sorry if I didn't give enough information.

Sheet 1 has 239407 records, names in A emails in B.

Sheet 2 has 52555 records, names in A emails in B.

I am not very good with formulas so I haven't been able to get the ones both of you gave to work.

Thanks!

Yes, but I copied it down and it seems like every cell in the column says #VALUE!

Is that right?

Also, how do I apply the auto filter and filter it to those that do not show zero?

Thanks so much for all the help.

Sorry, forgot to lock the cell refs in the formula. Try =COUNTIF(SHEET2!B$2:B$52555,B2) and then copying it down. You should have none that say #VALUE! then. (fingers crossed!)

As for adding the filter, select any cell in your header row (row 1 for example) and type "Alt D F F" (if you're using Excel 2010 you can type them one after the other, if using Excel 2003 you will have to type DFF whilst holding down Alt).

That will apply the filter, then select the filter arrow in the relevant column (column C in my example above).
How you restrict it to non zeros will differ depending on which Excel version you're using at the moment, but neither should be difficult to fathom.

Let me know if you get stuck.

Thanks, but it is still saying value and it keeps opening a window like I am supposed to browse and find a file. Sheet 1 and 2 are in the same workbook, is that a problem?

You have to make sure the "Sheet2" part of the formula is the same as the sheet you're looking up from.

So if that is "Sheet 2" (with a space) rather than "Sheet2" (without a space) then alter the formula to reflect that.

Alternatively, post a small sample of the workbookand I'll put the formula in for you so you can just copy and past from that into your proper workbook.

Well I feel dumb, the space was the problem.

Thank you all so incredibly much for you help. I was able to get it to work.