Numbering Duplicates in a list

[ClimoC]

Hey all

is there a quick and easy way of numbering duplicates in a list? Sort of in the same way that Windows chucks a (1) or a (2) on the end of files with the same name?

I have this list (and lists like it in the future) in my table

MPR25CS
MPR25CE
MPR25CS
MPR25CS
MPR25CE
MPR26CS
MPR26CS
MPR26CE

and I need it to sequentially, top to bottom, turn this list into this (always starting with '1' for the for occurance)

MPR25CS01
MPR25CE01
MPR25CS02
MPR25CS03
MPR25CE02
MPR26CS01
MPR26CS02
MPR26CE01



?

 

[barry houdini]

You could do that with a formula in the next column, e.g. with list starting at A2 use this formula in B2 copied down

=A2&TEXT(COUNTIF(A$2:A2,A2),"00")

 

[VoG]

Try this: formula in B1 copied down

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:94px;" /><col style="width:125px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td >MPR25CS</td><td >MPR25CS01</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >MPR25CE</td><td >MPR25CE01</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >MPR25CS</td><td >MPR25CS02</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >MPR25CS</td><td >MPR25CS03</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >MPR25CE</td><td >MPR25CE02</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >MPR26CS</td><td >MPR26CS01</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >MPR26CS</td><td >MPR26CS02</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td >MPR26CE</td><td >MPR26CE01</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=A1&TEXT(COUNTIF<span style=' color:008000; '>(A$1:A1,A1)</span>,"00")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[ClimoC]

Ok, sorry I forgot to mention.

In VBA please. I already knew how to do this but I don't have a 'spare' column to the side and it's an automated code generator for thousands of items at a time, click and dragging formulas wouldn't be the easiest fit.

I'm thinking some sort of other array formula expressed in VBA (these days my vba is better than my formulas)

Cheers
C

 

[VoG]

Try

Sub Dupes()
Dim LR As Long, i As Long, X() As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
ReDim X(1 To LR)
For i = 1 To LR
    With Range("A" & i)
        X(i) = WorksheetFunction.CountIf(Range("A1:A" & i), .Value)
    End With
Next i
For i = 1 To LR
    With Range("A" & i)
        .Value = .Value & Format(X(i), "00")
    End With
Next i
End Sub

 

[ClimoC]

Glorious. Sexy. Perfection. Essence. VoG for Men, by Dolce & Gabbana

(it works, thanks very much!!!)


C