Formulas in Conditional Formatting

Headline31

New Member
Joined
Nov 30, 2017
Messages
13
I am looking for a way to file through thousands of lines of data and to pick out certain duplicate items. I feel like I am just spitting out all of my thoughts, but hopefully someone will understand the gist. Using this table as an example, the thought train goes like this:

1. Check if column A has duplicates
No: skip
Yes: check if Column C has more than 1 unique item
No: skip
Yes: highlight all duplicate cells in Column A through Column C

***IF column A has a duplicate AND column C has more than 1 unique item, THEN highlight***

With that train of thought, all cells with "a" and "c" would be highlighted (including the blank cell in column C for "a")

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Column A
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Column C
[/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]a
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]b
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]c
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]c
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]c
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Welcome to the Forum!

Conditionally formatted using formula:

<countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>=MAX(COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,$C$1:$C$11)) < COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,"<>")

Excel 2010
ABC
1a1
2a2
3a
4b1
5b
6c1
7c3
8c2
9d
10d2
11d2
1


I don't know if example D is a possibility? My formula treats this as not "more than one unique item"</countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>
 
Last edited:
Upvote 0
StephenCrump the formula does work on a small scale, but when I try to apply it to almost 30,000 lines of data, Excel freezes or runs very slow if it completes. Any remedies?
 
Upvote 0
This should be much less computationally heavy:

=COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,"<>")>COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,INDEX($C$1:$C$11,MATCH(1,($A$1:$A$11=$A1)*($C$1:$C$11<>""),)))

Or, if column C will always be numeric:

=COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,AVERAGEIF($A$1:$A$11,$A1,$C$1:$C$11))<countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>")

The formula could be made much simpler if you didn't mind having the column C blank cells not highlighted.

But I wouldn't be using conditional formatting to identify possible problems in 30,000 rows. That's an awful lot to be scrolling through looking for highlighted rows.</countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>
 
Upvote 0
This should be much less computationally heavy:

=COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,"<>")>COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,INDEX($C$1:$C$11,MATCH(1,($A$1:$A$11=$A1)*($C$1:$C$11<>""),)))

Or, if column C will always be numeric:

=COUNTIFS($A$1:$A$11,$A1,$C$1:$C$11,AVERAGEIF($A$1:$A$11,$A1,$C$1:$C$11))<countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>")

The formula could be made much simpler if you didn't mind having the column C blank cells not highlighted.

But I wouldn't be using conditional formatting to identify possible problems in 30,000 rows. That's an awful lot to be scrolling through looking for highlighted rows.</countifs($a$1:$a$11,$a1,$c$1:$c$11,"<>

Ok I appreciate that information. I apologize, I am a little bit newer to these things in excel. Would there be an easier way to attack 30,000 lines since that will be pretty standard? I have dabbled in macros a little bit but am very open to hearing other suggestions.
 
Upvote 0
No apology necessary.

Perhaps you could give us a bit more information on what you're trying to do?

Presumably the original screenshot is an overly simplified example of something more complex.

Once you have highlighted /identified all the A and C rows, what next?

Are these perhaps the filtered results that you want to work with? Or perhaps the error rows that need elimination?
 
Upvote 0
Essentially the rows that get highlighted are data that may require additional review. I wouldn't be opposed to them being moved to a different tab once sorted if that's possible.
 
Upvote 0
Perhaps you could generate TRUE/FALSE in a spare column, and filter the results:


Excel 2010
ABCDE
1
2Header AHeader CREVIEW?
3a1TRUE
4a2TRUE
5aTRUE
6b1FALSE
7bFALSE
8c1TRUE
9c3TRUE
10c2TRUE
11dFALSE
12d2FALSE
13d2FALSE
Sheet1


Code:
Sub Test()

    Dim lLastRow As Long
    Const ROW1 = 2
    Const SPARE_COLUMN = "E"
    
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    With Range(SPARE_COLUMN & ROW1).Resize(lLastRow - ROW1 + 1)
        .Formula = "=COUNTIFS(A$" & ROW1 & ":A$" & lLastRow & ",A" & ROW1 & ",C$" & ROW1 & ":C$" & lLastRow & ",""<>"")>COUNTIFS(A$" & ROW1 & ":A$" & lLastRow & ",A" & ROW1 & ",C$" & ROW1 & ":C$" & lLastRow & ",INDEX(C$" & ROW1 & ":C$" & lLastRow & ",MATCH(1,(A$" & ROW1 & ":A$" & lLastRow & "=A" & ROW1 & ")*(C$" & ROW1 & ":C$" & lLastRow & "<>""""),)))"
        .FormulaArray = .FormulaR1C1
        .Value = .Value
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .Range("A1").Value = "REVIEW?"
    End With
    
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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