Count and ignore when duplicated (preferably in a pivot)

[Grizlore]

Hi

Can you help please?

I have a large set of data and I would like to be able to count the Incident Numbers in column A, however these Incident Numbers are duplicated down column.

Example of data in column A
20990
21011
21011
21025
21025
21025
21025
21025
21025

Example of the required result

20990 = 1
21011 = 1
21025 = 1

Preferably I would like to do this in a pivot (Excel 2003) However i dont think I can.

I think i need to add in a column which will look up and return a "1" when the number first appears, and "0" when it is duplicated. Then I can use this in my pivot.

Can anyone help in suggesting to me a formula which will return this please?

Thanks in anticapation.

 

[Robert Mika]

Try this:
XL-CENTRAL.COM : Excel : Count Unique Values

Excel 2013 have native ability to count unique values with your 2003 the link provided is the best answer.

 

[Grizlore]

Thanks for reply, although these just SUM the occurences, whic i can do in a pivot table.

I need to ignore any numbers which are duplicated, i need to count the numbers once, even if they appear several times.

 

[Robert Mika]

C2 down are you criteria
20990
21011
.
.
Then in D2 confirm COntrl+Shift+Enter drag down
=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",IF($A$2:$A$10=$C3,MATCH("~"&$A$2:$A$10,$A$2:$A$10&"",0))),ROW($A$2:$A$10)-ROW($A$2)+1),1))
Extend your ranges