Countif 13,800 rows

[blossomthe2nd]

Hi guys

I have a column that has 13,816 rows, I am basically looking to find any and all dupes within column,

I have simply typed the formula =Countif(A:A,A2) in Column B and left the sheet off to calculate the numbers ( its taking quite some time!!!)

However, once it is saying cal 100%, When I filter a result say 15 in column B, I would expect to see 15 examples of the same account in Column A , however this is not the case, its almost as if the formula is going halfway down the sheet , then stopping to calculate.


Is there a way to ensure that it has finished calclating, or perhaps a more user friendly way of getting these dupes ?

Thanks

 

[marka87uk]

After it's finished calculating, copy the cells and then Paste Special > Values.

 

[blossomthe2nd]

Hi Mark,

I have done that , thats how I noticed that the results were not correct , I pasted values then filtered in 15 but there were only actually 3 matches for them !!!

Its all weird, I have repeated the process about 3 times and I am continually getting incorrect results ( Although the same incorrect results, if you know what I mean !!)

Do you know a VBA that might help, maybe the data is just too much !!!
Thanks

 

[Kaps_mr2]

use countif(a$1:a$13816,a2) rather than the whole of column A. thanks

regards

Kaps

 

[Andrew Poulsom]

A pivot table may be quicker - numbers in the row area and count of numbers in the data area. You can then sort the row field by count of number descending.

 

[blossomthe2nd]

Guys Thanks for your responses,

Kaps, I tried your suggestion and still finding the same issue. Ive gone so far as to reboot PC !!!

Andrew, thanks for your suggestion, Im not over familiar with pivot tables, I see you stae about sorting the data and stuff, This work is for a diff depart and they have report based on line number ( Yep!!) and I need to keep it in the same sequence , can I do a pivot table and retain the line numbers etc ?

Thanks

 

[blossomthe2nd]

I have put a sequence Number on the data now and sort numberically then used the following formula which seems to work ( as its a smaller range per calc I assume!!)

Then I just need to figure out howmany instances of each dupe, but ill hopefully have a much smaller data range,

=IF(E3=E2,1,IF(E2=E1,1,0))

Thanks guys

 

[Kaps_mr2]

If you want to identify the duplicates, why cant you sort ?

If you need to retain the original order, then put a helper column in - as values. Sort by your data originally, and then when you need the original order sort by the helper column. thanks

regards

Kaps

 

[blossomthe2nd]

Hi guys

I have, used the if formua to see how many are duplicates, got a result of 650, copied these to another spreadsheet , just to be sure !! Then tried my countif formula and still have the same issue where the formula returns 15 when there are only 3 and so on ,

This has to be a data error, (Unless I have actually gone crazy!!) Im going to revist the data,