how do i highlight the values in a *filtered* list?

[bigdan]

I want take a list (which doesnt have any highlights), filter it, and then have the items that are duplicated there get highlighted.

Please see pic.

https://imgur.com/yTBncCM

In this list, at the top we can see Toronto is mentioned multiple times. That's irrelevant. If you'll look at what happens when we filter it (row 11), Toronto is only mentioned once, and Montreal is mentioned twice. So I'd like Montreal to be highlighted, not Toronto.

I do a reconciliation daily, which would involve me to filter something tens of times, maybe like 50+. So I'd really like to find something that would just take a few steps. If a solution would require me to do 4 steps for every filter, that wouldn't work for me. Of course if there's many steps involved just the first time and after that it's much more automated, that's fine.

 

[Marcelo Branco]

Maybe using this formula in CF (see in English)

Select B2:B8
Home > Conditional Formatting > New > Use a formula to determine...
=AND(SUBTOTAL(3,A$2:A$8)<>COUNTA(A$2:A$8),INDEX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B$2:B$8,ROW(B$2:B$8)-ROW(B$2),0,1)),MATCH(B$2:B$8,B$2:B$8,0)),ROW(B$2:B$8)-ROW(B$2)+1),MATCH(B2,B$2:B$8,0))>1)

Before filter

Pasta2
	A	B
1	List	City
2	ABC	Toronto
3	DEF	Toronto
4	GHI	Montreal
5	ABC	Montreal
6	DEF	Montreal
7	GHI	Montreal
8	ABC	Toronto
Plan1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=E(SUBTOTAL(3;A$2:A$8)<>CONT.VALORES(A$2:A$8);ÍNDICE(FREQÜÊNCIA(SE(SUBTOTAL(3;DESLOC(B$2:B$8;LIN(B$2:B$8)-LIN(B$2);0;1));CORRESP(B$2:B$8;B$2:B$8;0));LIN(B$2:B$8)-LIN(B$2)+1);CORRESP(B2;B$2:B$8;0))>1)	text	NO

Applying filter

Pasta2
	A	B
1	List	City
2	ABC	Toronto
5	ABC	Montreal
8	ABC	Toronto
Plan1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=E(SUBTOTAL(3;A$2:A$8)<>CONT.VALORES(A$2:A$8);ÍNDICE(FREQÜÊNCIA(SE(SUBTOTAL(3;DESLOC(B$2:B$8;LIN(B$2:B$8)-LIN(B$2);0;1));CORRESP(B$2:B$8;B$2:B$8;0));LIN(B$2:B$8)-LIN(B$2)+1);CORRESP(B2;B$2:B$8;0))>1)	text	NO

Or

Pasta2
	A	B
1	List	City
3	DEF	Toronto
6	DEF	Montreal
Plan1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=E(SUBTOTAL(3;A$2:A$8)<>CONT.VALORES(A$2:A$8);ÍNDICE(FREQÜÊNCIA(SE(SUBTOTAL(3;DESLOC(B$2:B$8;LIN(B$2:B$8)-LIN(B$2);0;1));CORRESP(B$2:B$8;B$2:B$8;0));LIN(B$2:B$8)-LIN(B$2)+1);CORRESP(B2;B$2:B$8;0))>1)	text	NO

Or

Pasta2
	A	B
1	List	City
4	GHI	Montreal
7	GHI	Montreal
Plan1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=E(SUBTOTAL(3;A$2:A$8)<>CONT.VALORES(A$2:A$8);ÍNDICE(FREQÜÊNCIA(SE(SUBTOTAL(3;DESLOC(B$2:B$8;LIN(B$2:B$8)-LIN(B$2);0;1));CORRESP(B$2:B$8;B$2:B$8;0));LIN(B$2:B$8)-LIN(B$2)+1);CORRESP(B2;B$2:B$8;0))>1)	text	NO

Or

Pasta2
	A	B
1	List	City
3	DEF	Toronto
4	GHI	Montreal
6	DEF	Montreal
7	GHI	Montreal
Plan1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B8	Expression	=E(SUBTOTAL(3;A$2:A$8)<>CONT.VALORES(A$2:A$8);ÍNDICE(FREQÜÊNCIA(SE(SUBTOTAL(3;DESLOC(B$2:B$8;LIN(B$2:B$8)-LIN(B$2);0;1));CORRESP(B$2:B$8;B$2:B$8;0));LIN(B$2:B$8)-LIN(B$2)+1);CORRESP(B2;B$2:B$8;0))>1)	text	NO

Hope this helps

M.