Faster formula for identifying duplicates

kwp004

Board Regular
Joined
Dec 27, 2016
Messages
91
I have a large excel file that I work with (>250K records). I have a need to remove duplicates. Normally, I use the formula below to identify the first unique value (then I drop the others), but it takes forever!

=(COUNTIF($C$2:$C2,$C2)=1)+0

The way the data is organized any duplicates are sequential (i.e. always next to each other). I'm extremely confident there will never be more than 25 unique duplicates in the dataset. Is there another formula I can use that would work faster? I think excel is searching all 250K+ records each time for a duplicate, when it only needs to search 25 - 50.

Thanks.
 

Attachments

  • 1601342367767.png
    1601342367767.png
    3.7 KB · Views: 6

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,326
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Would be helpful if you could post a sample of your data using XL2BB.
Have you considered using Data>Data Tools>Remove Duplicates ?? As an alternative, Data>Sort & Filter>Advanced Filter which can produce unique values.

I've never used either on more than a few thousand rows of data so don't know if either would be faster.
 

kwp004

Board Regular
Joined
Dec 27, 2016
Messages
91
Thank you both. Marcelo -- your approach worked like a charm! 20 minutes to 2 seconds :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,284
Members
410,908
Latest member
Allen P
Top