Duplicate Item Numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lehigh46 - 12 Jun 2007 12:40 GMT

In a privious post I asked the following question;

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.

As it turns out I gave a poor example of what I needed.

************** Apoligies to Teethless mama ****************

The following is what I really need.

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

Thanks for your help

Reply to this Message

Roger Govier - 12 Jun 2007 12:53 GMT

Hi

Then you only need to modify the formula Ron gave to you originally
=IF(COUNTIF($A$1:$A$24,A2)=1,"",COUNTIF($A$1:$A$24,A2))

Signature

Regards

Roger Govier

> In a privious post I asked the following question;
>
[quoted text clipped - 44 lines]
>
> Thanks for your help

Reply to this Message

Domenic - 12 Jun 2007 12:59 GMT

Assuming that A2:A28 contains the data, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER...

B2, copied down:

=IF(COUNTIF($A$2:$A$28,A2)>1,SUM(IF(COUNTIF($A$2:$A$28,$A$2:$A$28)>1,IF(A
2>$A$2:$A$28,1/COUNTIF($A$2:$A$28,$A$2:$A$28))))+1,"")

Hope this helps!

> In a privious post I asked the following question;
>
[quoted text clipped - 44 lines]
>
> Thanks for your help

Reply to this Message

Duke Carey - 12 Jun 2007 14:49 GMT

Without sorting your column A values, it'll get tremendously complicated.

Here's what will work if they are sorted:

In cell B2 use the formula
=IF(COUNTIF($A$2:$A$28,A2)=1,"",1)

In cell B3 use this formula & copy it down
=IF(COUNTIF($A$2:$A$28,A3)=1,"",IF(COUNTIF(A$2:A3,A3)>1,VLOOKUP(A3,A$2:B2,2,0),1+MAX(B$2:B2)))

> In a privious post I asked the following question;
>
[quoted text clipped - 44 lines]
>
> Thanks for your help

Reply to this Message