Answered by:
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.
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

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
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


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:=xlToLeftEnd sub
Thanks.
 Edited by Zoyas Monday, June 15, 2015 8:47 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 
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