Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-numbering sequence (Excel 2003)

    I want to be able to create a macro that will do the following
    From a Column with information as below, and add 001 to the end.

    Amazon
    Amazon
    America
    American
    American
    TheAmer
    Analyser

    If there is aready a matching word, then the 001 would increase by 1. The end result being

    Amazon001
    Amazon002
    America001
    American001
    American002
    Analyser001
    Billing001

    I am somewhat rusty in my VBA skils, I have not done this for some time (at 2yrs) and need some help fast!
    Any information would be gratefully appreciated

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-numbering sequence (Excel 2003)

    Will the list always be sorted in ascending order?

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-numbering sequence (Excel 2003)

    Yes.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-numbering sequence (Excel 2003)

    OK, try this macro. Select the cells you want to number, then run the macro.

    Sub AutoNumber()
    Dim n As Long
    Dim r As Long
    Dim strPrev As String
    For r = 1 To Selection.Rows.Count
    If Selection.Cells(r, 1) = strPrev Then
    n = n + 1
    Else
    strPrev = Selection.Cells(r, 1)
    n = 1
    End If
    Selection.Cells(r, 1) = Selection.Cells(r, 1) & Format(n, "000")
    Next r
    End Sub

  5. #5
    Lounger
    Join Date
    Mar 2003
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-numbering sequence (Excel 2003)

    Phenominal. Worked like a charm.

    IT would have taken me a least a day to figure that out with my limited rusty skills! THANK YOU - You have saved me manually having to do this on 3000 plus rows. I know have a better weekend ahead of me!

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto-numbering sequence (Excel 2003)

    Incidentally, if your data started in A1, you could enter this formula in B1 and copy down:
    <code>=A1&TEXT(COUNTIF(A$1:A1,A1),"000")</code>
    You could then copy and Paste Special-> Values if you wished.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •