How to list and count unique records

[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.

 

[T. Valko]

You can use the advanced filter to extract the uniques. See this:

http://contextures.com/xladvfilter01.html#FilterUR

Then you can use a simple COUNTIF for the count of each unique.

 

[Bernd P]

Hello,

I suggest to use my UDF Lfreq or Lfreq2:
http://sulprobil.com/html/lfreq.html

Some other options I list here:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[Ron Rosenfeld]

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.

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

 

[Jayanta Boral]

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,