Duplicate Item Numbers

[lehigh46]

Hi All,

Column A has hundreds of item numbers.
Most are NOT duplicated, but I could have as many as 10 to 15 item
numbers with duplications and each number could be duplicated 3 to 10
times or more.

Question:

I need a formula for column B which will return a unique number for
each set of duplicates.

I appriciate any help you can give me.

 

[Ron de Bruin]

You can insert this formula in B2 with your first data cell in A2 and copy down
I use the data range A1:A24 in this example (A1 = the header)

=IF(COUNTIF($A$1:$A$24,A2)=1,"Unique",IF(COUNTIF($A$1:$A$24,A2)=0,"Empty","Duplicate("& COUNTIF($A$1:A2,A2)&")"))

 

[Ron de Bruin]

DataRefiner can insert the formulas for you if you want
http://www.rondebruin.nl/datarefiner.htm

 

[lehigh46]

DataRefiner can insert the formulas for you if you want
http://www.rondebruin.nl/datarefiner.htm

Ron,
Thanks for the formula. It's not exactly what I was looking for but I
can use this in other applications. It works well with one of my other
spread sheets.

The end result of what I want would look like the example below.
I could then auto filter column B and just see the occurences of item
number 4, or 5, or 10, etc.

A B
1
2
3
4 1
4 1
4 1
5 2
5 2
6
7
8
9
10 3
10 3
10 3
10 3
10 3
10 3
10 3
10 3
24
25 4
25 4
26
27 5
27 5
27 5

 

[Guest]

Assume row 1 either a header row or blank row and your data start in A2:A28

In B2: =IF(COUNTIF($A$2:$A$28,A2)>1,MAX($B$1:B1)+(A1<>A2),"")

copy down

 

[lehigh46]

Thanks Roger. this is it

 

[lehigh46]

I ment to thank DOMINIC........... apoliogies

Thanks Roger. this is it

 

[Roger Govier]

I completely misread your question.

Domenic got it right.