Excel Format duplicate values - change the text in the cell

Question

It is very easy to format the cells that have duplicated values (like setting specific background on them or something other style) using the "Conditional formatting", but how can I change their text?

For example:

A1 2332

A2 2333

A3 2334

A4 2334

to become:

A1 2332

A2 2333

A3 2334(1)

A4 2334(2)

Excellll · Accepted Answer · 2012-03-02T15:12:09.300

One way to do this is to just add a second column next to your original data with the following formula filled down:

=IF(COUNTIF($A$1:$A$5000,A1)>1,A1& " (" & COUNTIF(A$1:A1,A1) & ")",A1)

where your original data is in A1:A5000. Beware that COUNTIF is pretty inefficient, so if you have a lot of data, this could take a while to calculate and affect your workbook performance.

For a large workbook, I'd consider using a VBA Worksheet_Change event to edit the values in place. This code should be inserted in the appropriate Worksheet module. On 5000 test records it had a couple second lag.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim dataRng As Range
Dim dataArr() As Variant, output() As String
Dim y As Long, i As Long, j As Long, tmpcount As Long

'Change "A1" to address of start of column you want to index.
Set dataRng = Range("A1").Resize(Me.UsedRange.Rows.Count, 1)
If Not Intersect(Target, dataRng) Is Nothing Then
    dataArr = dataRng.Value
    ReDim output(1 To UBound(dataArr, 1), 1 To 1)
    'Strip old counts from data once in array.
    For y = 1 To UBound(dataArr, 1)
        If Right(dataArr(y, 1), 1) = ")" Then
            dataArr(y, 1) = Left(dataArr(y, 1), InStr(dataArr(y, 1), " (") - 1)
        End If
    Next y

    For i = 1 To UBound(dataArr, 1)
        tmpcount = 0
        output(i, 1) = dataArr(i, 1)
        For j = 1 To UBound(dataArr, 1)
            If dataArr(i, 1) = dataArr(j, 1) Then
                tmpcount = tmpcount + 1
                If j = i And tmpcount > 1 Then
                    output(i, 1) = dataArr(i, 1) & " (" & tmpcount & ")"
                    Exit For
                End If
                If j > i And tmpcount > 1 Then
                    output(i, 1) = dataArr(i, 1) & " (" & tmpcount - 1 & ")"
                    Exit For
                End If
            End If
        Next j
    Next i
    Call printoutput(output, dataRng)
End If

End Sub


Private Sub printoutput(what As Variant, where As Range)
Application.EnableEvents = False
where.Value = what
Application.EnableEvents = True
End Sub

Beware I've made a couple of big assumptions:

I assumed the column you want to index starts at A1. If it is in another column, you will need to adjust line 7 of the code.
I assumed your data will never end in ")" unless it has previously been indexed. If that's not the case, stay away from this code!

Excel Format duplicate values - change the text in the cell

1 Answers1