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?
 

azairvine

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

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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

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

Well-known Member
Joined
Aug 12, 2009
Messages
1,558
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:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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
 

azairvine

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

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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

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

Forum statistics

Threads
1,081,678
Messages
5,360,472
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top