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 ?

 

[Aladin Akyurek]

Why do you need to copy down this formula?

What is data you have in column E - numbers or text?

 

[earthworm]

Text.

 

[earthworm]

Why do you need to copy down this formula?

What is data you have in column E - numbers or text?

Text . Please also suggest solution for numbers as well if any.

 

[Aladin Akyurek]

COUNTIF is an expensive function. Alternatives will be expensive too if copied down to a huge number of cells. By the way, you did not answer the question of why you need to copy down this formula.

 

[earthworm]

COUNTIF is an expensive function. Alternatives will be expensive too if copied down to a huge number of cells. By the way, you did not answer the question of why you need to copy down this formula.

I need to identify 1 instance only and then extract .

I have find an alternative . Using advance filter to extract unique record seems feasible .

However Now i m confused . How can i extract duplicate / more then one instance record through advance filter

 

[Aladin Akyurek]

I need to identify 1 instance only and then extract .

I have find an alternative . Using advance filter to extract unique record seems feasible .

This is a quite adequate method to extract unique records.

However Now i m confused . How can i extract duplicate / more then one instance record through advance filter

That is also possible.

Let E5 house a header, something like #Items #.

Let F4 empty.
In F5 enter:

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

Now select F4:F5.
Fire up Advanced Filter.
Click OK when it complains.
Check the option for Copy to another location.
For List range: Select the relevant range including the header.
For Criteria range: Select $F$5:$F$6.
For Copy to: Select the F7 cell.
Click OK.

If done properly, Advanced Filter will deliver an output consisting of items which occur only twice.

 

[earthworm]

This is a quite adequate method to extract unique records.



That is also possible.

Let E5 house a header, something like #Items #.

Let F4 empty.
In F5 enter:

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

Now select F4:F5.
Fire up Advanced Filter.
Click OK when it complains.
Check the option for Copy to another location.
For List range: Select the relevant range including the header.
For Criteria range: Select $F$5:$F$6.
For Copy to: Select the F7 cell.
Click OK.

If done properly, Advanced Filter will deliver an output consisting of items which occur only twice.

Not working.

Please refer sheet.

https://app.box.com/s/0y7lhqkfavf3kx6im9gcy08yyxdlbkiw

 

[Peter_SSs]

Not working.

If you are just looking to identify items that occur multiple times, try
=COUNTIF($E$6:$E$14,E6)>1

 

[Aladin Akyurek]

Not working.

Please refer sheet.

https://app.box.com/s/0y7lhqkfavf3kx6im9gcy08yyxdlbkiw

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, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] "][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]items[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B050]#00B050[/URL] , 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>}}​