How to list and count unique records

  • Thread starter Thread starter mikeg
  • Start date Start date
M

mikeg

I am trying to tabulate errors from an automated checking tool.
The errors are listed by line. There can be hundreds of results but
mostly there are less than a dozen errors found repeatedly. I would
like to list the unique errors that occur, and list a count for how
many occurrences there are for each of those errors.

In example, assuming there are only three unique errors in a list of
20, I want a result like this (and the number can be in a separate
cell; I don't need to concatenate the error and value together in one
string)
First error 5
Second error 11
Third error 4

By defining a name, I've been able to count the unique errors using an
array -- =SUM(1/COUNTIF(Errors,Errors)). But I want that count broken
down by errors.
 
I am trying to tabulate errors from an automated checking tool.
The errors are listed by line. There can be hundreds of results but
mostly there are less than a dozen errors found repeatedly. I would
like to list the unique errors that occur, and list a count for how
many occurrences there are for each of those errors.

In example, assuming there are only three unique errors in a list of
20, I want a result like this (and the number can be in a separate
cell; I don't need to concatenate the error and value together in one
string)
First error 5
Second error 11
Third error 4

By defining a name, I've been able to count the unique errors using an
array -- =SUM(1/COUNTIF(Errors,Errors)). But I want that count broken
down by errors.
Click to expand...


From your description, you might be able to use a pivot table. If you drag the
errors to the rows area, and also to the Data or Value area, you should be able
to generate a count of each error type.
--ron
 
You can use pivot table function to get a quick solution

Suppose you have the following error listing -

record# errortype
1 e1
2 e2
3 e2
4 e1
5 e3
6 e2
7 e4
8 e5
9 e5
10 e6
11 e2
12 e3
13 e1

select the data array (or table). click at data, pivot table and pivot chart
report.

the pivot table wizard will now open.

select errortype from field list and drag it to row area
again select errortype from field list and drag to data area. you will find
"count of errortype" to appear in the data area. if "sum of errortype"
appers then please double click this and choose count function.

click next.

The wizard will now ask where to put the pivot table. You can choose a new
worksheet or any cell in any worksheet.

Click finish.

The following result will appear in pivot table -

Count of errortype
errortype Total
e1 3
e2 4
e3 2
e4 1
e5 2
e6 1
Grand Total 13

hope this is ok.

regards,
 
Back
Top