| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to remove duplicated data repeated in a column and keep just o | Excel Worksheet Functions | |||
| Counting repeated occurances | Excel Worksheet Functions | |||
| How can i stop same data being repeated in a column | Excel Discussion (Misc queries) | |||
| How can i stop data being repeated within a column? | Excel Discussion (Misc queries) | |||
| Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) | |||
Hybrid Mode
