Formulas in Conditional Formatting

[Headline31]

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")

Column A		Column C
a		1
a		2
a
b		1
b
c		1
c		3
c		2

<tbody>
</tbody>

 

[StephenCrump]

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
	A	B	C
1	a		1
2	a		2
3	a
4	b		1
5	b
6	c		1
7	c		3
8	c		2
9	d
10	d		2
11	d		2
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,"<>

 

[Headline31]

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?

 

[StephenCrump]

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,"<>

 

[Headline31]

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.

 

[StephenCrump]

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?

 

[Headline31]

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.

 

[StephenCrump]

Perhaps you could generate TRUE/FALSE in a spare column, and filter the results:

Excel 2010
	A	B	C	D	E
1
2	Header A		Header C		REVIEW?
3	a		1		TRUE
4	a		2		TRUE
5	a				TRUE
6	b		1		FALSE
7	b				FALSE
8	c		1		TRUE
9	c		3		TRUE
10	c		2		TRUE
11	d				FALSE
12	d		2		FALSE
13	d		2		FALSE
Sheet1

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