Help counting duplicate values

[Mchgh9]

Hi,

I have a large amount of data that contains a lot of duplicate records. The records can have different states but I just want to count the number of times a specific cell appears in the list of data, without removing duplicates.

[TABLE="width: 571"]
<TBODY>[TR]
[TD]Tag?</SPAN>[/TD]
[TD]Insp. Date</SPAN>[/TD]
[TD]Insp. Time</SPAN>[/TD]
[TD]Insp.</SPAN>[/TD]
[TD]Inspection Type Description</SPAN>[/TD]
[TD]Inspection Outcome</SPAN>[/TD]
[TD]Insp. OD</SPAN>[/TD]
[TD]Promoter Reference</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]18/12/2003</SPAN>[/TD]
[TD]15:01</SPAN>[/TD]
[TD]RTN</SPAN>[/TD]
[TD]Routine Category B</SPAN>[/TD]
[TD]PASSED</SPAN>[/TD]
[TD]LA001</SPAN>[/TD]
[TD]MERS00069694</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]07/06/2005</SPAN>[/TD]
[TD]11:55</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]GT001</SPAN>[/TD]
[TD]MERS00075412</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]19/08/2005</SPAN>[/TD]
[TD]00:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]GT001</SPAN>[/TD]
[TD]CC59463</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]13/11/2006</SPAN>[/TD]
[TD]15:03</SPAN>[/TD]
[TD]D/2</SPAN>[/TD]
[TD]Defect Follow Up Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]27/11/2006</SPAN>[/TD]
[TD]16:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]03/01/2007</SPAN>[/TD]
[TD]09:05</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]30/01/2007</SPAN>[/TD]
[TD]13:04</SPAN>[/TD]
[TD]D/2</SPAN>[/TD]
[TD]Defect Follow Up Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]23/03/2007</SPAN>[/TD]
[TD]11:01</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]20/04/2007</SPAN>[/TD]
[TD]09:00</SPAN>[/TD]
[TD]D/2</SPAN>[/TD]
[TD]Defect Follow Up Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212</SPAN>[/TD]
[/TR]
[TR]
[TD]N</SPAN>[/TD]
[TD]09/05/2007</SPAN>[/TD]
[TD]12:00</SPAN>[/TD]
[TD]D/1</SPAN>[/TD]
[TD]Defect Joint Site Visit Category C</SPAN>[/TD]
[TD]FAIL-LOW RISK</SPAN>[/TD]
[TD]PN001</SPAN>[/TD]
[TD]WR125212

</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=7><COL></COLGROUP>[/TABLE]

This is a small sample of the data. As you can see in the works reference WR125212 appears a number of times. There are approx 20,000 rows with various different references in this data.

Is there any way I can count the number of unique reference numbers, possibly using a pivot table or a formula?

Thanks

Dave

 

[TheCman81]

You can use something like this

=SUM(IF(FREQUENCY(MATCH($G$2:$G$17,$G$2:$G$17,0),ROW($G$2:$G$17)-1),1,0))

 

[EstherBezzy]

Hi Dave,

You can also do this with a pivot table. Put the "Promoter Reference" field in both the Values and Row areas. Since it's text, the default calculation in the pivot will be Count. Should look omething like the table below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of Promoter Reference[/TD]
[/TR]
[TR]
[TD]CC59463[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MERS00069694[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MERS00075412[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]WR125212[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]



Now, if only I could figure out how to post an image here to give the actual illustration....

Best regards,
Esther

 

[TheCman81]

Now, if only I could figure out how to post an image here to give the actual illustration....

Best regards,
Esther

You can use the HTML Maker

http://www.mrexcel.com/forum/excel-questions/89356-how-display-your-sheet-board-html-maker-faq.html