Rename Duplicate values using vba

• 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

• 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 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 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 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 WithColumns("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:=xlToLeftEnd sub`

Thanks.

• Edited by 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 Friday, June 19, 2015 9:03 AM
Thursday, June 18, 2015 7:04 AM