formula help for finding first of duplicate entries

[Grizlore]

Hi All,

Can you help?

I have a huge list in column A which contains serial numbers. I need to count how many serial number there are, so i need to ignore any duplicates in the list.

I am trying to create a formula which puts a "1" in column B when the first serial number appears, and a "0" when it is duplicated. Then i'll pivot them and count those with a "1"

Can anyone help please?

Help appreciated

 

[Snakehips]

Grizlore,

Why not select column A, Advanced Filter , Tick Unique records only.

Then bottom left of window status bar will show how many are unique of total.

Hope that helps.

 

[Grizlore]

i have loads and need to pivot them... I have Excel 2003, which i dont think does Advanced / unique

 

[Snakehips]

Grizlore,

Assuming your data starts A2 then in B2 and copy down as required, try....

=IF(COUNTIF(A$2:A2,A2)=1,1,0)

If you can pivot on ones and any higher integer rather than require ones and zeros then maybe just...

=COUNTIF(A$2:A2,A2)