Simple for Some (count the duplicates)

[markobrien]

I have a spreadsheet with 5 columns (it actually a fault report for
network I have received), the first column is my main 'primary' colum
and describes the type of fault reported.

The thing is there are probably 12,453 fault entries and there ar
1,500 different types of faults. How do I find out how many times
fault (sorted by the primary column) appears int he table.

I don't want to filter 1,500 times and manually write down the amoun
of entries..any help greatly appreciated. What I need to end up with i
a spreadsheet, with 1,500 entry types and how many there are.

It might be a bit far fetched, thank-you for any help

mar

 

[Nikos Yannacopoulos]

Mark,

Sort your table by fault type and then use Autofilter (Data > Filter >
Autofilter). The wizzard is straightforward, just choose function Count.

HTH,
Nikos

 

[Leo Heuser]

Mark

Here's one way:
Assuming data in Sheet1, Types of faults in A2:A13000,
Heading in A1.
Resultlist to be put in sheet2.

1. Enter Sheet2 (!!)
2. Choose Data > Filter > Advanced filter
3. Check "Copy to another place" (or similar)
4. Click in "Listrange" (or similar) and select
A1:A13000 in Sheet1 (or enter: Sheet1!A1:A13000)
5. Click in "Criteriarange" (or similar) and select an
empty cell in sheet2
6. Click in "Copy to" and select A1 (e.g.)
7. Check "Unique records only" (or similar), Click OK.
8. In Sheet2 B2 enter the formula:
=COUNTIF(Sheet1!$A$2:$A$13000,A2)
9. Copy B2 down with the fill handle (the little square in
the lower right corner of the cell)
10. Done

 

[Frank Kabel]

Hi
I would create a pivot table for this. See the following
sites for more details about this:
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