Alternate to COUNTIF(E$6:E6,E6)

[earthworm]

I have a huge data set and i need to identify duplicate . I am using the formula COUNTIF(E$6:E6,E6) but this formula slows the calculation significantly in huge list approx 65000 rows. Is there any fastest formula ?

 

[earthworm]

It works as advertised. Using your file, I get:

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]items[/TD]
[TD="align: center"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]items[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

As you see, with:

=COUNTIF($E$6:$E$14,E6)=2

in F5, we get doublets, that is, items that occur only twice.

If you want Advanced Filter to display only the names of doublets, check the Unique records only box before you hit OK.

For triplets, set up the criteria range in G4:G5 for example, with G5 housing:

=COUNTIF($E$6:$E$14,E6)=3

For all items which occur twice or more, invoke:

=COUNTIF($E$6:$E$14,E6)>1

​

_{^{<strike>
</strike>}}​

what keys are you pressing as its still not working this is what I am doing

Suppose my data is present in Colum C Starting from row 4 till 12
I entered the formula in cell D3 next to items
I click on advance filter and it automatically selects the range next to it along with headers
in criteria i select the criteria cell and then click ok
its showing complete list

 

[Aladin Akyurek]

what keys are you pressing as its still not working this is what I am doing

Suppose my data is present in Colum C Starting from row 4 till 12
I entered the formula in cell D3 next to items
I click on advance filter and it automatically selects the range next to it along with headers
in criteria i select the criteria cell and then click ok
its showing complete list

Here is your own file: https://www.dropbox.com/s/8cvtm28i36ow3n6/earthworm Sample sheet.xlsx?dl=0

1. Leave F4 empty.

2. Enter the following formula in F5 (for getting dublets, items which which occur twice).

=COUNTIF($E$6:$E$14,E6)=2

3. Select F4:F5.

4. Run Data | Filter | Advanced.

5. Click in the box for List range. Select E5:E14.

6. Click in the box for Criteria range. Select F4:F5.

7. Click in the box fo Copy to. Select F6.

8. Decide whether you want to check Unique records only.

9. Click Ok.

 

[earthworm]

Here is your own file: https://www.dropbox.com/s/8cvtm28i36ow3n6/earthworm Sample sheet.xlsx?dl=0

1. Leave F4 empty.

2. Enter the following formula in F5 (for getting dublets, items which which occur twice).

=COUNTIF($E$6:$E$14,E6)=2

3. Select F4:F5.

4. Run Data | Filter | Advanced.

5. Click in the box for List range. Select E5:E14.

6. Click in the box for Criteria range. Select F4:F5.

7. Click in the box fo Copy to. Select F6.

8. Decide whether you want to check Unique records only.

9. Click Ok.

thanks now its working . strange that previously i did exactly what you told but it wasn't working .but now it is

might be i was making error which i dont realize . thanks again for your great help. at-least now my work will be done .

 

[Aladin Akyurek]

thanks now its working . strange that previously i did exactly what you told but it wasn't working .but now it is

might be i was making error which i dont realize . thanks again for your great help. at-least now my work will be done .

Great to hear that. Thanks for the update.