Forum Discussion

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

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

SergeiBaklan

Diamond Contributor

Jul 17, 2023

subbucat

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.

subbucat
Copper Contributor
Jul 17, 2023
I made the changes to the formula

=SUMPRODUCT(--(TRIM( IFERROR( LEFT($B$2:$C$4111, SEARCH("(",$B$2:$B$4111)-1 ), $B$2:$B$4111) ) = TRIM( IFERROR( LEFT($B2, SEARCH("(",$B2)-1 ), $B2) ) ) ) > 1
- SergeiBaklan
  Diamond Contributor
  Jul 17, 2023
  subbucat
  
  At least it shall be
  
  ...LEFT($B$2:$B$4111)
  - subbucat
    Copper Contributor
    Jul 18, 2023
    SergeiBaklan Thank you. This has cleared the problem upto an extent. But now it is searching the whole sheet with all columns. whereas my search should be limited to A and B Columns.
    
    ALB Gjirokastra -Expired as of XXXX 112 Gjirokastra -Expired as of XXXXXX
    Technically this is not duplicate but it is highlighting as duplicate because the same name exits in Column D.