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.

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/2...le-parameters/

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

"freeman" <(E-Mail Removed)> wrote in message
news:63EBC19F-3868-4DA5-9168-(E-Mail Removed)...
>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.
>
>

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" wrote:
> 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.
>
>

Thank you, This will do the job.

"Bernard Liengme" wrote:

> 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/2...le-parameters/
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "freeman" <(E-Mail Removed)> wrote in message
> news:63EBC19F-3868-4DA5-9168-(E-Mail Removed)...
> >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.
> >
> >
>
>
>