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 column A and sort the data to show duplicates one after another. The city names have the same cell values but have additional information in parenthesis, and this is proving to be tougher to highlight the duplicates as excel is treating them as unique values. Below is the sample data. I would greatly appreciate if you could help me with a solution.
| AFG | Alotau |
| AFG | Bamiyan (Silk Road) |
| AFG | Elsewhere |
| AFG | Herat & Jalalabad |
| AFG | Kabul |
| AFG | Kabul (Gandarmack and Intercontinental) |
| AFG | Kabul (Kabul Serena) |
| AFG | Kandahar & Mazar e Sharif |
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) ) ) ) > 1
