Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need code to find duplicate numbers and continue to count the number of times the duplicate was found.

Example: I have 20,000 entries and 303 unique numbers. I need to know how many times each number has been duplicated. So from this:

1 2 
1 3
1 2
3 3
52 1
52 300

and 1000s more I want to count the number of times each value occurs in the first column like so:

1  3 (This means we saw `1` three times)
3  1
52 2

How can I do that in Excel or should I use other software for this?

share|improve this question
    
See also. –  pnuts May 9 '13 at 9:44

3 Answers 3

Assuming your data is in A:B and labelled in Row1:

=COUNTIF(A:A,A2)

in Row2 of another column (in example below C) and copied down as appropriate will count all instances. (Basically the same formula as @tdk2fe but slightly simpler and more versatile).

Then if required pivot the two columns as shown for a better view of the quantities (as suggested by @Travis with only reference to details how.)

SU593099 example

Remove Duplicates is not wholly reliable (best avoided in my view, Data > Sort & Filter - Advanced, Unique records only etc is my preference) (ref).

(Edited to adjust image to match actual sample data whose layout was not well displayed in OP at time of previous version.)

share|improve this answer

I believe the best option for this would be to create a Pivot Table. If you know exactly what data you wanted to count and it was a very finite data set you could use the COUNTIF(range, criteria) function.

The Pivot Table in Excel has the ability to do the counts on dynamic data, meaning you aren't sure exactly what you are looking for.

The depth of pivot tables would be too great to post in an answer so please refer to this link:

http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.html

share|improve this answer

I would create a new column, and then use the 'Remove Duplicates' function. After that, for each value in the column, use the COUNTIF() function.

COUNTIF(A1:A20000, 1) <-- returns the number of times '1' shows up in column A
share|improve this answer
    
This seems to imply a rather laborious hard coding of the criterion, though having extracted duplicates I doubt you intended that! Note Remove Duplicates is 'suspect'. –  pnuts May 8 '13 at 15:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.