Counting repeated data in a column

[freeman]

I have an excel document that has column A2-A6140 that list data that 3509
items are duplicated. What I am looking for is a formula that can tell me how
many times an item is repeated.

For example

AIMAS1D
AIMAS1D
AIMAS2D
AIMAS3D
AIMAS3U
AIMAS4D
AIMAS4U
AINAS1R
AOLDB1P1
AOLDB3P1
AOLDB3P1


In Column B I need to list how many items appear twice, in column C three
times, etc.

Any formula, VBA, or macro would be appreciated.

 

[Bernard Liengme]

In B2 use =COUNTIF($A$2:$A$6140,A2)
and copy down the row
But a pivot table would be neater. See one of these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[Max]

If you want just the pure counts in a col,
then in B2, copied down: =COUNTIF(A:A,A2)

If you're after a set-up to display it in multiple cols, maybe ..
In B1 across, enter the numbers: 1,2,3,...
Then in B2: =IF(COUNTIF($A:$A,$A2)=B$1,"x","")
Copy across/fill down as far as required to populate
--
Max
Singapore
http://savefile.com/projects/236895
Files: 352, Subscribers: 53, Downloads: 15,500
xdemechanik

 

[freeman]

Thank you, This will do the job.

In B2 use =COUNTIF($A$2:$A$6140,A2)
and copy down the row
But a pivot table would be neater. See one of these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email