Forum Discussion

subbucat's avatar
subbucat
Copper Contributor
Jul 17, 2023

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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 17, 2023

subbucat 

As variant, if you are on 365 or 2021

with

=SUM(--(TRIM( TEXTBEFORE($C$3:$C$10, "(",,,1 ) ) = TRIM( TEXTBEFORE($C3, "(",,,1 ) )) ) > 1
  • subbucat's avatar
    subbucat
    Copper Contributor
    Jul 17, 2023
    Pro plus 2021
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 17, 2023

      subbucat 

      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) ) ) ) > 1
      • subbucat's avatar
        subbucat
        Copper Contributor
        Jul 17, 2023

        SergeiBaklan I tried the 2nd formula and almost all rows got highlighted, except few. (The first one did not work). Thank you for helping

         

         

Resources