Conditional Formatting Duplicates Based on Number of Duplicates

snewsome

New Member
Joined
May 12, 2015
Messages
41
Hey Everyone,

I'm having a hard time finding a solution to finding duplicates based on the number of duplicates found. I know how to setup CF to find duplicates but what if I want to change the CF to highlight the cells depending on how many times they're duplicated. EXAMPLE: if a double match is found highlight Yellow, if a triple match is found highlight orange....etc. Hope this makes sense. I'm trying to create a 2,3,4, and 5 duplicate CF. Hoping this is possible.
PLEASE HELP! Thanks so much!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hello,

If you really wanted to use conditional formatting - please use it in moderation. If you copy and paste conditional formatting for multiple criteria in many cells it is a lot for Excel to calculate and it will invoke calculation every time you do anything. There are alternative solutions...

My helper column in column B works off my criteria in column A:
I have headers in Row 1
B2=COUNTIF($A$1:A2,A2)
This provides me with the occurrence number as you referred to.

Now I can create conditional formatting on column A 'Use a formula to determine which cells to format'.
Formula: =B2:B5=1
Applies to: =$A$2:$A$5

so on each cell in the formula array if the criteria is 1 then I have chosen a colour and applied this check to the respective range in column A. This method can now be duplicated to set up colours for each criteria (2, 3 etc..). I would consider dynamic named ranges to make this more flexible.

May I ask why you would want to do this?
 
Upvote 0
Use a CF formula such as:

=AND(A1<>"",COUNTIF(A:A,A1)=2)

Repeat for =3, =4 and =5. What will you be doing for >5?
 
Upvote 0
Here is a VBA solution that could be easily modified if you wanted to account for even more duplicates.

Code:
Const Red = 242
Const lRed = 7171583
Const Orange = 1019133
Const Yellow = 458488
Const Green = 917365

Sub cf5()
Dim r As Range
Dim cel As Range

Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))

For Each cel In r
    With cel.Interior
        Select Case Application.WorksheetFunction.CountIf(r, cel)
            Case 1
                .Color = Green
            Case 2
                .Color = Yellow
            Case 3
                .Color = Orange
            Case 4
                .Color = lRed
            Case 5
                .Color = Red
        End Select
    End With
Next cel

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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