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:

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Jim
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]Bill
[/TD]
[TD]Mark
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]Jim
[/TD]
[/TR]
[TR]
[TD]Bill
[/TD]
[TD]Shane
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]Bill
[/TD]
[/TR]
</tbody>[/TABLE]

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?

 

[azairvine]

I have determined that if I use an expanding range on the COUNTIF statement I can get a running total of sorts that I could implement into the conditional formatting to get the desired result.

The issue I'm having now though is that I need the COUNTIF to count from top left to bottom right. There are 5 columns of data and x rows that I want it to count in the array. Everything works fine except in the circumstances where count #2 & #3 are in the same row. For example:

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD]Bill
[/TD]
[TD]Bob
[/TD]
[TD]Jane
[/TD]
[TD]Jim
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]Bill
[/TD]
[TD]Sam
[/TD]
[TD]Shane
[/TD]
[TD]Bill
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Currently both "Bill" on the 2nd row would be highlighted as the "running count" increments by row, thus the COUNTIF for "Bill" and "Bill" would both return 3 - I need the COUNTIF for "Bill" to return 2, and for "Bill" to return 3 (triggering the conditional formatting).

 

[Misca]

Say your range is A2:B2. Select the whole range from A2 on => Conditional Formatting / New Rule / Use a formula to determine which cells to format:

=COUNTIF($A$2:$B2;A2)>2

Just make sure you lock the first cell and the last column of your range.

 

[azairvine]

Say your range is A2:B2. Select the whole range from A2 on => Conditional Formatting / New Rule / Use a formula to determine which cells to format:

=COUNTIF($A$2:$B2;A2)>2

Just make sure you lock the first cell and the last column of your range.

Thank you for your help!

This solution works in all scenarios except that mentioned in post #2 . As can be seen from the attached image:

The first Bob on the second row shouldn't be highlighted as it is only the second instance of Bob from top left to bottom right.

 

[Misca]

You're going to need to use two ranges for your next example:

=(COUNTIF($A$1:$E1;A2)+COUNTIF($A2:A2;A2))>2

The first range is for all the rows above and the next one is for the row you're on.

 

[Trebor76]

Not sure if you're interested in a VBA solution, but as long as your data set isn't too large this macro will do the job:

Option Explicit
Sub Macro1()
    
    Dim varMyArray() As Variant
    Dim lngLastRow As Long
    Dim rngMyCell As Range
    Dim lngArrayCount As Long
    Dim lngArrayIndex As Long
    Dim dblMyCounter As Double
    
    Application.ScreenUpdating = False
    
    'Finds the last row for the columns used. Change to suit.
    lngLastRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For Each rngMyCell In Range("A3:E" & lngLastRow) 'Works for the range A3:E[lngLastRow]. Change to suit.
        If Len(rngMyCell) > 0 Then
            lngArrayCount = lngArrayCount + 1
            ReDim Preserve varMyArray(1 To lngArrayCount)
            varMyArray(lngArrayCount) = rngMyCell
            dblMyCounter = 0
            For lngArrayIndex = LBound(varMyArray) To UBound(varMyArray)
                If StrConv(varMyArray(lngArrayIndex), vbProperCase) = StrConv(rngMyCell, vbProperCase) Then
                    dblMyCounter = dblMyCounter + 1
                    If dblMyCounter >= 3 Then
                        rngMyCell.Interior.Color = RGB(255, 0, 0) 'Colours entries 3 or more in red. Change to suit.
                        Exit For
                    Else
                        rngMyCell.Interior.Color = xlNone
                    End If
                End If
            Next lngArrayIndex
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[azairvine]

Not sure if you're interested in a VBA solution, but as long as your data set isn't too large this macro will do the job:

Regards,

Robert

Thanks Robert,

I'm using Google Sheets and I have a feeling that limits me significantly when it comes to VBA scripts?

 

[azairvine]

You're going to need to use two ranges for your next example:

=(COUNTIF($A$1:$E1;A2)+COUNTIF($A2:A2;A2))>2

The first range is for all the rows above and the next one is for the row you're on.

This works perfectly......except the actual sheet is a bit more complex (I was trying to keep it as simple as possible in the forum).

This is a clipping of the actual sheet:

The data is in "blocks" of 4 columns (col D-G, H-K, L-O, etc). The conditional formatting applies to the "Card" column (col E, I, M etc) and works perfectly as I said - but I want the conditional formatting to apply to each 4 column "block". You can see the formatting that is currently existing in orange and blue (based on the "Val" columns) as an example. The conditional formatting for these has been created with separate rules per 4 column "block" - which obviously I couldn't replicate as it would break the COUNTIF as is my understanding.

tl;dr

It works great, I just need it to apply the conditional formatting across a 4 cell "block" each time it's triggered.

 

[Trebor76]

I'm using Google Sheets and I have a feeling that limits me significantly when it comes to VBA scripts?

No idea actually as I've not used them It seems like you've got the nifty formula Misca has provided which is great.

 

[azairvine]

No idea actually as I've not used them It seems like you've got the nifty formula Misca has provided which is great.

I wrote a lot of VBA scripts probably 6 years ago or so when I was designing QA sheets for my employer - but I've forgotten all of that entirely! In fact my brain is so mush that it took me til now to figure out that I just need to essentially transpose the formula Misca gave me to the other three columns (while referring to the Card column each time) to solve my latest issue with it.