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

azairvine

New Member
Joined
Nov 17, 2018
Messages
17
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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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:

Bill
Bob
Jane
Jim
John
Bill
Sam
Shane
Bill
John

<tbody>
</tbody>

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).
 
Upvote 0
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.
 
Upvote 0
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:

Fe7pWqF.jpg


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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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:

Code:
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
 
Upvote 0
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?
 
Upvote 0
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:

K9OI2mh.jpg


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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top