Forum Discussion
Duplicate values with partial matches
Hello Gurus, i have an excel sheet with 3000 rows with column A named countries and another column B named cities. I need to highlight the duplicate city names in column B for each country in col...
As variant, if you are on 365 or 2021
with
=SUM(--(TRIM( TEXTBEFORE($C$3:$C$10, "(",,,1 ) ) = TRIM( TEXTBEFORE($C3, "(",,,1 ) )) ) > 1
Pro plus 2021
If above doesn't work you may try
=SUMPRODUCT(--(TRIM( IFERROR( LEFT($C$3:$C$10, SEARCH("(",$C$3:$C$10)-1 ), $C$3:$C$10) ) = TRIM( IFERROR( LEFT($C3, SEARCH("(",$C3)-1 ), $C3) ) ) ) > 1SergeiBaklan I tried the 2nd formula and almost all rows got highlighted, except few. (The first one did not work). Thank you for helping
That's hard to comment without seen conditional formatting rule formula and range to which it applied. Finally desirably to see exact values for which the rule doesn't work.