Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    making duplicate cells unique by adding counter

    I have a large spreadsheet with two columns. The second column contains a lot of duplicates. I would like to add a counter to each duplicate cell, thus making each unique.

    The current contents of the cells in column two are, say:

    abcd
    abcd
    abcd

    I would like to end up with:

    abcd
    abcd-1
    abcd-2

    If I were a programmer, I would write a macro that loops through the spreadsheet, finds the duplicate cells and adds a counter to each, until all cells were unique. Unfortunately I can't write such code.

    I would be very grateful if someone could help me.

    Regards
    useful

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Vba

    U,

    This code will add the counter as you described to the duplicates.

    Hope you find it useful, Useful.

    Maud

    Counter1.png Counter2.png
    Code:
    Public Sub AddCounter()
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row  'FIND LAST ROW
    counter = 1
    For I = 2 To LastRow  'CYCLE THROUGH ALL VALUES
        base = Cells(I, 2).Value  'GET BASE VALUE
        For K = I + 1 To LastRow  'CYCLE THROUGH REMAINING VALUES
            compare = Cells(K, 2).Value  'GET VALUE TO COMPARE TO BASE
            If base = compare Then  'IF MATCH THEN...
                Cells(K, 2).Value = compare & "-" & counter  'ADD THE COUNTER
                counter = counter + 1  'INCREMENT COUNTER
            End If
        Next K
        counter = 1  'RESET COUNTER
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-07-15 at 21:18.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    useful (2013-07-16)

  4. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Hi Maud

    Thank you for the prompt response and the code. You are wonderful, writing code that doesn't even require the column sorted to have all the duplicates together in sequence.

    Thank you for enabling me to be useful.

    Regards
    useful

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Useful,

    You were right on the money with the logic to do it!

Posting Permissions

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