Highlighting "duplicates" only AFTER they have appeared two times....

[azairvine]

I've read a lot about highlighting duplicates, or all values that appear x times - but my problem is a little more complex than that.

I want to only highlight cells that appear 3+ times (excluding the first two occurrences).

For example:

Jim	Bob
Bill	Mark
Jane	Jim
Bill	Shane
Jim	Bill

<tbody>
</tbody>

In the above example, only the bottom row of cells should be highlighted as they are the 3rd instance of "Jim" and the 3rd instance of "Bill". I want the first two of any string to not be highlighted.

Is there some way to do a rolling "Count" conditional formatting that will check cells sequentially from top left to bottom right in a given range?

 

[Trebor76]

NP

If anyone comes across this thread and would like a VBA solution, I have tweaked my original macro with some great code Peter_SSs provided in this thread which stops the need to loop through the varMyArray to determine how many times the rngMyCell value is in it:

Option Explicit
Sub Macro1()
    
    Dim varMyArray() As Variant
    Dim lngLastRow As Long
    Dim rngMyCell As Range
    Dim lngArrayIndex As Long
    
    Application.ScreenUpdating = False
    
    'Assumes the data is spread across columns A to E (inclusive). Change to suit.
    lngLastRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For Each rngMyCell In Range("A2:E" & lngLastRow) 'Works for the range A2:E[lngLastRow]. Change to suit.
        If Len(rngMyCell) > 0 Then
            lngArrayIndex = lngArrayIndex + 1
            ReDim Preserve varMyArray(1 To lngArrayIndex)
            varMyArray(lngArrayIndex) = rngMyCell
            If UBound(Filter(varMyArray, rngMyCell, True, 1)) + 1 >= 3 Then 'Adapted from https://www.mrexcel.com/forum/excel-questions/689201-count-number-specific-values-array-vba-2.html
                rngMyCell.Interior.Color = RGB(255, 0, 0) 'Colours entries 3 or more in red. Change to suit.
            Else
                rngMyCell.Interior.Color = xlNone
            End If
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[azairvine]

NP

If anyone comes across this thread and would like a VBA solution, I have tweaked my original macro with some great code Peter_SSs provided in this thread which stops the need to loop through the varMyArray to determine how many times the rngMyCell value is in it:

Regards,

Robert

Awesome Robert! Thanks a lot for your contribution