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.

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)&")"))


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"lehigh46" wrote in message ...
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.

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
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)&")"))


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"lehigh46" wrote in message ...
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.

On Fri, 8 Jun 2007 19:29:30 +0200, "Ron de Bruin"
wrote:

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

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" wrote:

On Fri, 8 Jun 2007 19:29:30 +0200, "Ron de Bruin"
wrote:

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

Thanks Roger. this is it





On Fri, 08 Jun 2007 13:09:40 -0400, lehigh46
wrote:

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.

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



On Tue, 12 Jun 2007 08:18:33 -0400, lehigh46
wrote:



Thanks Roger. this is it





On Fri, 08 Jun 2007 13:09:40 -0400, lehigh46
wrote:

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.

I completely misread your question.

Domenic got it right.

--
Regards

Roger Govier


"lehigh46" wrote in message
...


Thanks Roger. this is it





On Fri, 08 Jun 2007 13:09:40 -0400, lehigh46
wrote:

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.