Help counting duplicate values

Mchgh9

New Member
Joined
Feb 2, 2012
Messages
39
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.

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

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=7><COL></COLGROUP>

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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

TheCman81

Well-known Member
Joined
Feb 28, 2012
Messages
535
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

New Member
Joined
Aug 14, 2014
Messages
2
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.

Row LabelsCount of Promoter Reference
CC594631
MERS000696941
MERS000754121
WR1252127
Grand Total10

<tbody>
</tbody>



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

Best regards,
Esther
 

Watch MrExcel Video

Forum statistics

Threads
1,095,752
Messages
5,446,301
Members
405,396
Latest member
SpiFF2000

This Week's Hot Topics

Top