Conditional Format to highlight dupes on EACH column BUT only on visible filtered selection

[Melimob]

Hi, I want to basically do the inbuilt 'Highlight Duplicate Values' that excel applies against each column in my data set. = Easy.
However, when I filter the selection, I want it to consider the filtered selection and tell me where there are dupes.

I thought I got it to work with this formula but it's not working all the time and I think it's comparing columns. I want to look at each column individually and perform the same way the usual CF dupe works but just change according to what is filtered.

SUMPRODUCT(($E$21:$E$919=$E21)*($U$21:$U$919=$U21)*SUBTOTAL(103,OFFSET($E$21,ROW($E$21:$E$919)-ROW($E$21),0)))>1

My data set is A21:U919 however I only want the rules to apply to E21:U919 (it's in a table btw).

Any help would be greatly appreciated.

thank you!

 

[felixstraube]

You want the highlight duplicates to apply to every column individually. But for each column you want to apply the criteria from column U?
With you range E21:U919 selected try to enter this formula in the CF:

=SUMPRODUCT((E$21:E$919=E21)*($U$21:$U$919=$U21)*SUBTOTAL(103,OFFSET($E$21,ROW($E$21:$E$919)-ROW($E$21),0)))>1

The column criteria without fixing the column. No "$" for the column.

 

[Melimob]

You want the highlight duplicates to apply to every column individually. But for each column you want to apply the criteria from column U?
With you range E21:U919 selected try to enter this formula in the CF:

=SUMPRODUCT((E$21:E$919=E21)*($U$21:$U$919=$U21)*SUBTOTAL(103,OFFSET($E$21,ROW($E$21:$E$919)-ROW($E$21),0)))>1

The column criteria without fixing the column. No "$" for the column.

Firstly, HUGE HUGE Thanks for your reply... it nearly works however I think it's my error not understanding the formula against what I want to achieve.
I want to compare every column to itself and highlight as dupe based on filtered selection. I.e. not comparing to column U?
thank you again! really helps!

 

[Melimob]

Firstly, HUGE HUGE Thanks for your reply... it nearly works however I think it's my error not understanding the formula against what I want to achieve.
I want to compare every column to itself and highlight as dupe based on filtered selection. I.e. not comparing to column U?
thank you again! really helps!

OK so based on your guidance,, I removed the absolute ref from U also and IT WORKS!!!

thank you again.. this is what worked:

SUMPRODUCT((E$21:E$919=E21)*(U$21:U$919=U21)*SUBTOTAL(103,OFFSET($E$21,ROW($E$21:$E$919)-ROW($E$21),0)))>1

Note: takes a while for it to colour as you scroll left/right but catches up in the end!
great thank you!

 

[felixstraube]

Glad you could solve it.
Thanks for the feedback!