Forum Discussion
Compare comma separated cell contents, show double occurrences
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.
Oops, I believe I answered on that, but my reply for some strange reason disappeared. Anyway, just in case another variant of PQ solution.
