Highlight duplicate values in excel

问题:

I need to highlight the duplicate values from a cell to a list in excel.

Here is the input cell(In One cell)

1A - London,Perth,Dublin

List to compare

2A  Paris
3A  London
4A  New York
5A  Dublin
6A  Perth
7A  Washington
8A  Mumbai
9A  Monacco
10A Dublin
11A Shanghai
12A London

I need to highlight the duplicate values. Eg. London, Perth and Dublin Should get Highlighted(Dublin & London appears twice so it should be highlighted twice)

回答1:

Try conditionally formatting A2:A12 with the following formula:

=IF(ISERROR(SEARCH("*"&$A2&"*", $A$1)>0), FALSE, TRUE) (see screenshots below).