Faster formula for identifying duplicates

[kwp004]

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.

 

[JoeMo]

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.

 

[Marcelo Branco]

If the values are grouped and beginning in C2 this should be faster
=--(C2<>C1)
copy down

M.

 

[kwp004]

Thank you both. Marcelo -- your approach worked like a charm! 20 minutes to 2 seconds

 

[Marcelo Branco]

Well, it looks faster!

Glad to help.

M.