excelduplicate-data

Excel Format duplicate values - change the text in the cell


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)


Solution

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

    1. 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.
    2. 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!