|
Counting repeated data in a column
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. |
Counting repeated data in a column
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" wrote in message ... 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. |
Counting repeated data in a column
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. |
Counting repeated data in a column
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" wrote in message ... 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. |
| All times are GMT +1. The time now is 11:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com