Count quantity of repeated items in a column

[Susienak]

I have a list that contains numbers that repeat Example: 900,825,827,900,
765,827,765, 765, 902, 900, etcc... I want to know how many times each number
is repeated in that column. What function in Excel 2007 do I use so that I
know 900 was repeated 340 times and 825 was repeated 23 times. There are
thousands of numbers repeated thousands of times so how do I found out how
many times each number is repeated? Can someone explain this step by step
because Im new to Excel...

Thanks,

Susan

 

[Joel]

Let put the count only the first time the number is found. Assume the
numbers start in A1. Put this formula in B1 and then copy down Column B

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF($A:$A,A1),"")

Countif will count the numbe of times a number appears.

**** First Countif ******

COUNTIF(A$1:A1,A1)=1 - Notice the dollar sign in front of the 1. This
will fix the first cell of the range when you copy down column B

B1 = COUNTIF(A$1:A1,A1)=1
B2 = COUNTIF(A$1:A2,A2)=1
B3 = COUNTIF(A$1:A3,A3)=1
B4 = COUNTIF(A$1:A4,A4)=1
B5 = COUNTIF(A$1:A5,A5)=1

**** Second Countif ******

B1 = COUNTIF($A:$A,A1)
B2 = COUNTIF($A:$A,A2)
B3 = COUNTIF($A:$A,A3)
B4 = COUNTIF($A:$A,A4)
B5 = COUNTIF($A:$A,A5)

The 2nd Countif counts the number of times the number appears in Column A
($A:$A) where the 1st Countif counts only the number of times the number
appears in the rows equal or above where the formula occurs.

The test for equal one will only be true the first time a number appears in
column A

the IF statement works like this

=if(Condition,true,false)


In the formula the false is not display anything which is "" the two sets
of double quotes.


So the formula

 

[Susienak]

Joel... All that does is tell me the amount of times one number repeats. I
literally have a million numbers and probably 40,000 of those are unique so
the rest of them are repeats and I want to know how many times each of those
unique numbers repeats. I cant put that formula into every box 40,000 times.
There isnt a formula that will tell me every unique number and the number of
times it repeats?

So say column a has 100,000 boxes starting with A2 all the way to A100,001.
Of all of those numbers about 400 are unique so what I want to know is how
many times each number repeats. There has to be a way to tell me all at once
that 900 appears 2,028 times, and 876 appears 560 times, etc... without
having to find each of the 400 unique numbers and then putting that formula
in for each of the 400 numbers.

Should I separate the unique numbers into their own column and then can a
formula compare column a to column b and give me totals for each unique
formula?

 

[Joel]

1) First, How can a UNIQUE number repeat?

2) The unique numbers are the ones that have a count of 1 in column B. You
can sort column B to find the numbers that appear only once.

3) Here is an easy method of copying the formula down column B

a) copy cell B1 - It will be highlighted.
b) Select cell A1
c) Press Control and Down - Arrow , this will get you to last cell in
Column A
d) Select the cell to the right of the highlighted cell. This will be the
last cell in Column B
e) Press Control and Up-arrow. Now Column B should be highlighted.
f) right click mouse and select Paste.

 

[Susienak]

Joel, A unique number or value is an Excel term... it takes alist and tells
you within the list all of the unique numbers so yes they do repeat. I
figured out the answer. I had to use Pivottable and drag and drop the entire
list (original column) into both rows and values on the bottom right and
poof... that gives you the unique values in one row and the times each unique
value repeats in the second row. If anyone out there needs me to explain
inmore detail just write to this post and I'll answer what I found. It was a
beyond easy answer that took less then 30 seconds... no formulas, no
functions, no copying, no separating... easy.