none
Rename Duplicate values using vba RRS feed

  • Question

  • Hello everyone,

    I have a spreadsheet with a large number of data. I have noticed it contains some duplicate data as well. For example, in A column of my spreadsheet(Sheet1) I have some alphanumeric data like 1ab,2ab,3cd,4cd which supposed to be unique all the time, but some times I have noticed the values are repeating. I want a macro that could help me to rename those duplicate values.

    Please refer to the below example:

    I have similar types of data in A columns.

    1ab
    1ab
    2cd
    2cd
    3cd
    4ef
    4ef
    4ef
    4ef

    I want to have a macro which will rename those duplicate values by adding "_data(Starting from 1 to n)" at the last. The output will be as follows:

    1ab_data1
    1ab_data2
    2cd_data1
    2cd_data2
    3cd
    4ef_data1
    4ef_data2
    4ef_data3
    4ef_data4

    Thanks.

    Monday, June 15, 2015 5:47 PM

Answers

  • Write this formula into cell B1 and drag it down to the end of your data:

    =IF(COUNTIF(A:A,A1)>1,A1&"_data"&COUNTIF(A:A,A1)-COUNTIF(A2:$A$1000,A1),A1)

    Then copy column B and paste the values into column A, finally delete column B.

    Andreas.

    • Marked as answer by Zoyas Tuesday, June 16, 2015 8:57 AM
    Monday, June 15, 2015 6:10 PM
  • If I want to apply this formula for all the data irrespective of whether it is duplicate or not, what exactly I need to change here?

    Remove the IF part:

    =A1&"_data"&COUNTIF(A:A,A1)-COUNTIF(A2:$A$1000,A1)

    Andreas.

    • Marked as answer by Zoyas Friday, June 19, 2015 9:03 AM
    Thursday, June 18, 2015 7:04 AM

All replies

  • Write this formula into cell B1 and drag it down to the end of your data:

    =IF(COUNTIF(A:A,A1)>1,A1&"_data"&COUNTIF(A:A,A1)-COUNTIF(A2:$A$1000,A1),A1)

    Then copy column B and paste the values into column A, finally delete column B.

    Andreas.

    • Marked as answer by Zoyas Tuesday, June 16, 2015 8:57 AM
    Monday, June 15, 2015 6:10 PM
  • Hi Andreas,

    Thanks for your reply. It would be better if I have this formula in programming form. Whatever you mentioned is absolutely correct but can you please make it in VBA? Actually I want to get the necessary results by clicking on a button.

    Thanks.

    Monday, June 15, 2015 7:23 PM
  • Hi Andreas,

    I have come up with the following code and it's showing the correct output. Please let me know if you have any better idea on this.

    Sub duplicate()

    Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With

    Columns("B:B").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("B1").Select
        Range("B1").Formula = "=IF(COUNTIF(A:A,A1)>1,A1&""_data""&COUNTIF(A:A,A1)-COUNTIF(A2:$A$1000,A1),A1)"
        Selection.AutoFill Destination:=Range("B1:B" & LastRow)
        Columns("B:B").Select
        Selection.Copy
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("B:B").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlToLeft

    End sub


    Thanks.



    • Edited by Zoyas Monday, June 15, 2015 8:47 PM
    Monday, June 15, 2015 8:38 PM
  • Hi Andreas,

    Just a small question for my understanding. If I want to apply this formula for all the data irrespective of whether it is duplicate or not, what exactly I need to change here?

    For example if my input is

    1ab
    1ab
    2cd
    2cd
    3cd
    4ef
    4ef
    4ef

    4ef


    I want to have the output in below format:

    1ab_data1
    1ab_data2
    2cd_data1
    2cd_data2
    3cd_data1
    4ef_data1
    4ef_data2
    4ef_data3
    4ef_data4

    Thanks
    Wednesday, June 17, 2015 6:21 PM
  • If I want to apply this formula for all the data irrespective of whether it is duplicate or not, what exactly I need to change here?

    Remove the IF part:

    =A1&"_data"&COUNTIF(A:A,A1)-COUNTIF(A2:$A$1000,A1)

    Andreas.

    • Marked as answer by Zoyas Friday, June 19, 2015 9:03 AM
    Thursday, June 18, 2015 7:04 AM