Compare comma separated cell contents, show double occurrences

Hello everybody, I am looking for a solution to the following problem. There are two cells (B26 & D26) with names separated by commas. The number of names is variable (should probably not exceed 40)...

excel

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Jan 09, 2021

sepp89117 I may be wrong but suspect this is not so easy with just a formula. However, when I take your example literally (i.e. compare two cells with comma separated strings and display "Error" if duplicates are noticed), perhaps the attached workbook contains an acceptable solution for you.

It uses PowerQuery (PQ) and requires two named ranges, each pointing at one of the two cells. In my example I named them "cellB26" and cellD26". The query creates separate tables from each cell and checks for matching records, through merging (Inner join) these two tables . If there are matching records, then "Error" will be returned in cell F26. If, on the other hand the merged table is empty (i.e. no matching records found) then F26 remains empty.

Change the content in cells B26 and/or D26 and press "Refresh All" on the Data ribbon and see if it does what you expect.

sepp89117.xlsx19 KB