Isolating Duplicates - Better Method Than Conditional Format and Filter?

I'm working on isolating duplicate values in a huge list of products (Over 200k rows). I'm familiar with conditional formatting to highlight duplicates, and when I try to filter on those, it bogs down my machine given the massive size of the file. Is there a faster/better way to find/isolate duplicate values? Perhaps something that would even pull them into a new spreadsheet?

Any advice/pointers would be greatly appreciated.

Thanks

Same spelling. Numeric or alphanumeric. So, for example, it could be ABC123EDF. If that shows up twice in the same column, I'd like to isolate it.

If your goal is to actually 'remove' the duplicates, then there is a very nice "Remove Duplicates" feature on the Data tab.

But actually identifying/flagging the duplicates is more difficult as you can see.

Another method might be to use a standard cell formula instead of conditional formatting..

In an adjescent column filled to the end.
=IF(COUNTIF(A$1:A1,A1)>1,"it's a dupe","")

Then filter on that column.


I don't know if that's any better than the current conditional formatting method..

Check this link for ideas:

http://excelribbon.tips.net/T005674_...e_Columns.html

I'll try the IF/COUNTIF formula and see if that speeds up the process. Not trying to remove the duplicates, definitely need to flag them.

Is it possible to modify one of those formulas so that it flags both occurrences of the duplicate as being a duplicate, instead of just the second occurrence? I want to be able to select both so that I can determine which to remove based on data in other columns.

Just make the range encompass the whole set of data.
But this will decrease the performance

=IF(COUNTIF(A$1:A$200000,A1)>1,"it's a dupe","")

If your goal is to actually 'remove' the duplicates, then there is a very nice "Remove Duplicates" feature on the Data tab.

This was exactly what I needed. Thanks!