ConditionalF in table too slow. Other solution?

[Weeble]

I have a table where column C are dates and column E are names.
I have a conditional formatting rule that checks for duplicates.. If date and name are duplicate, highlight red. My problem is that since I am using a table I can't specify a range ( excel don't support that yet? ) And right now I am at 12000 rows. When I want to filter it takes forever for excel.
Could I possibly use VBA to to the same thing but in a table range?

For example. VBA
IF Table1 updates
CHECK IF C and F = Duplicates
IF true = Highlight C to F
If false = break

 

[Fluff]

Whilst you cannot use table references in CF rules, if you select the table range when applying the rule, the range will automatically update if the table changes in size.

 

[Yongle]

This is how I do it using CF which restricts all ranges to the size of the table
I find it easier to use named ranges which automatically expand or contract with a table

Create 2 named ranges

T_Dates with refers to range
=Table1[Date]

T_Names with refers to range
=Table1[Name]

Select data (not header) in chosen columns (I only selected column C and then E)

Create CF rule with formula
=COUNTIFS(T_Dates,C2,T_Names,E2)>1

Results

Book1
	A	B	C	D	E	F	G
1	H1	H2	Date	H4	Name	H6	H7
2	Data1	Data2	01/10/2020	Data4	Joe	Data6	Data7
3	Data1	Data2	02/10/2020	Data4	Jack	Data6	Data7
4	Data1	Data2	03/10/2020	Data4	Jill	Data6	Data7
5	Data1	Data2	04/10/2020	Data4	Jenny	Data6	Data7
6	Data1	Data2	05/10/2020	Data4	Jason	Data6	Data7
7	Data1	Data2	01/10/2020	Data4	Joe	Data6	Data7
8	Data1	Data2	07/10/2020	Data4	Jack	Data6	Data7
9	Data1	Data2	08/10/2020	Data4	Jill	Data6	Data7
10	Data1	Data2	04/10/2020	Data4	Jenny	Data6	Data7
11	Data1	Data2	15/10/2020	Data4	Jason	Data6	Data7
12	Data1	Data2	01/10/2020	Data4	Joe	Data6	Data7
13	Data1	Data2	17/10/2020	Data4	Jack	Data6	Data7
14	Data1	Data2	18/10/2020	Data4	Jill	Data6	Data7
15	Data1	Data2	19/10/2020	Data4	Jenny	Data6	Data7
16	Data1	Data2	20/10/2020	Data4	Jason	Data6	Data7
17	Data1	Data2	01/10/2020	Data4	Joe	Data6	Data7
18	Data1	Data2	22/10/2020	Data4	Jack	Data6	Data7
19	Data1	Data2	23/10/2020	Data4	Jill	Data6	Data7
20	Data1	Data2	24/10/2020	Data4	Jenny	Data6	Data7
21	Data1	Data2	01/10/2020	Data4	Jason	Data6	Data7
22	Data1	Data2	01/10/2020	data4	joe	Data6	Data7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E22	Expression	=COUNTIFS(T_Dates,C2,T_Names,E2)>1	text	NO
C2:C22	Expression	=COUNTIFS(T_Dates,C2,T_Names,E2)>1	text	NO

 

[Fluff]

There's no need to use named ranges, as long as you originally set the applies to range to match the databody range ( either one column or multiple columns) the appliesto range will automatically change to match the size of the table.

 

[Yongle]

@Fluff - agreed but old habits die hard and the CF formula is simplified and immediately understandable
=COUNTIFS(T_Dates,C2,T_Names,E2)>1

My suggestion also selects the data in the table to determine the CF range

 

[Fluff]

old habits die hard

Agreed

 

[Weeble]

Whilst you cannot use table references in CF rules, if you select the table range when applying the rule, the range will automatically update if the table changes in size.

Ah, thank you. Useful to know in the future.

I was able to scale it down then to 12k rows. But my file is still extremly slow. If I try to filter excel will think about the process for a good minute before anything happends.
Might be my CF formula that makes it slow?

=SUMPRODUCT(($C$2:$C$27830=$C2)*($E$2:$E$27830=$E2))>1

 

[Fluff]

You can limit the formula to the range of the table as well & it will change automatically.
You can also use

=COUNTIFS($C$2:$C$8790,$C2,$E$2:$E$8790,$E2)>1

However it will still be slow.
Are the values in C & E manually entered or are they formulae?

 

[Weeble]

You can limit the formula to the range of the table as well & it will change automatically.
You can also use

=COUNTIFS($C$2:$C$8790,$C2,$E$2:$E$8790,$E2)>1

However it will still be slow.
Are the values in C & E manually entered or are they formulae?

There are no other formulas in the table. They are connected thrue a powerquery.
So i'm guessing as long as I use a CF on this kind of size, it will be slow?

Any other ideas on how I might be able to to do it?

 

[Fluff]

If the table is created with Power Query, then I don't know if there is another way of doing it, as I've never used PQ.