Is there a way to check for duplicate values in Excel WITHOUT using the CountIf function?

Question

A lot of the solutions here on SO involve using CountIf to find duplicates. When I have a list of 100,000+ values however, it will often take minutes for CountIf to search for duplicates.

Is there a quicker way to search for duplicates within an Excel column WITHOUT using CountIf?

Thanks!

EDIT #1:
After reading the comments and replies I realize I need to go into greater detail. Let's pretend I'm a birdwatcher, and after I return from a birdwatching trip I input anywhere from 1 to 25 or 50 new birds that I saw on my trip into my "Master List of Birds Seen". This is really a dynamically growing list, and with each addition I want to make sure I'm not duplicating something that already exists in my list.

So, in column A of my file are the names of the birds. Column B-M might contain other attributes of the birds. I want to know if a bird that I just added in column A after my latest birdwatching trip ALREADY exists somewhere ELSE in my list. And, if it does, I would manually merge the data of the 2 entries and throw away some and keep some after careful review. I clearly don't want to have duplicate entries of the same bird in my database.

So, ultimately I want some indication that there is or isn't a duplicate somewhere else, and if there is duplicate please tell me what row to look in (or highlight or color both of the duplicates).

Answer 1

The fastest way that I know of (in case you are using Excel 2007/2010/2011) is to use Data (In Ribbon) | Remove Duplicates to find the total number of duplicates OR to remove duplicates. You might want to move data to a temp sheet before you test this.

The 2nd fastest way is to use Countif. Now Countif can be used in many ways to find duplicates. Here are two main ways.

1) Inserting a New Column next to the data and putting the formula and simply copying it down.

2) Using Countif in Conditional formatting to highlight cells which are duplicates. For more details, please see this link.

suggestions for a macro to find duplicates in a SINGLE column

EDIT:

My Apologies :)

Countif is the 3rd fastest way!

The 2nd fastest way is to use Pivot Tables ;)

What exactly is your main purpose of finding duplicates? Do you want to delete them? Or Do you want to highlight them? Or something else?

FOLLOWUP

Seems like I made a typo in the formula. Yes for large number of rows, CountIf does take minutes as you suggested.

Let me see if I can come up with a VBA code to suit your exact needs.

Sid