Excel - Count quantity of repeated items in a column

Asked By Susiena on 30-Jul-08 09:04 PM
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




Joe replied on 30-Jul-08 10:47 PM
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
Joe replied on 31-Jul-08 07:53 AM
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.
Susiena replied on 31-Jul-08 12:05 PM
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.
Samuel replied on 22-May-09 11:37 AM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.