Single count of Duplicate values

[shantraj.antin@gmail.com]

Hi Team,

In the attached sheet I used the formula IF(SUMPRODUCT(($B$2:$B2=B2)*($J$2:$J2=J2))>1,0,1) to identify single count for duplicate values. With this formula my 6 Lakh line item worksheet is very slow. Can we replace this formula to increase the efficiency. Kindly advise if we can use any other formula to get the same result.

One more problem is I will have to sort the data A to Z for the result. will it be simplified.

Regards,
Raj

 

[Asheesh]

Just try this in cell C2...it should be comparatively faster...

IF(COUNTIF($B$2:B2,B2)=1,1,)

 

[Deepak]

Just removed if from Asheesh approach.

=--(COUNTIF($B$2:B2,B2)=1)

or countifs...

For the large data set use VBA..

 

[Asheesh]

Good trick Deepak

 

[shantraj.antin@gmail.com]

Thanks guys for the formula. But still excel is very slow when I past special values. Its taking longer time to apply for all the 6 lakh line items.

 

[Deepak]

Thanks guys for the formula. But still excel is very slow when I past special values. Its taking longer time to apply for all the 6 lakh line items.

That's why i suggested for VBA.