Compare comma separated cell contents, show double occurrences

%3CLINGO-SUB%20id%3D%22lingo-sub-2045259%22%20slang%3D%22de-DE%22%3ECompare%20comma%20separated%20cell%20contents%2C%20show%20double%20occurrences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2045259%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20solution%20to%20the%20following%20problem.%20There%20are%20two%20cells%20(B26%20%26amp%3B%20D26)%20with%20names%20separated%20by%20commas.%20The%20number%20of%20names%20is%20variable%20(should%20probably%20not%20exceed%2040).%20It%20must%20not%20happen%20that%20a%20name%20appears%20in%20both%20cells.%20I%20would%20like%20to%20have%20a%20control%20function%20in%20column%20F%20using%20an%20Excel%20formula%20that%20warns%20if%20a%20name%20occurs%20in%20cells%20B%20and%20D.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20would%20be%20grateful%20for%20any%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---German---%3C%2FP%3E%3CP%3EHello%20together%2C%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20solution%20to%20the%20following%20problem.%20There%20are%20two%20cells%20(B26%20%26amp%3B%20D26)%20in%20which%20there%20are%20names%20separated%20by%20comma.%20The%20number%20of%20names%20is%20variable%20(should%20probably%20not%20exceed%2040).%20There%20must%20be%20no%20name%20in%20both%20cells.%20I%20would%20like%20to%20have%20an%20Excel%20formula%20control%20function%20in%20column%20%3CSTRONG%3EF%3C%2FSTRONG%3E%20that%20warns%20if%20a%20name%20appears%20in%20cell%20%3CSTRONG%3EB%3C%2FSTRONG%3E%20and%20%3CSTRONG%3ED.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22750px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2246.9167px%22%3E%3CSTRONG%3ELine%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22127.75px%22%3E%3CSTRONG%3EB%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3Ec%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22263.167px%22%3E%3CSTRONG%3ED%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CSTRONG%3EE%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22231.367px%22%3E%3CSTRONG%3EQ%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2246.9167px%22%3E%3CSTRONG%3E26%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22127.75px%22%3ENameA%2C%20NameB%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22263.167px%22%3ENameC%2C%20NameD%2C%20NameA%2C%20NameE%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22231.367px%22%3E%3CP%3E%3Dif(a%20name%20in%20B26%20%3D%20a%20name%20in%20D26%3B%20%22Error!%22%3B%20%22%22)%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20Unfortunately%2C%20my%20boss%20wants%20it%20to%20be%20displayed%20like%20this%20...%20Unfortunately%2C%20the%20table%20cannot%20be%20modified.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2045259%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2045389%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20comma%20separated%20cell%20contents%2C%20show%20double%20occurrences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2045389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F923045%22%20target%3D%22_blank%22%3E%40sepp89117%3C%2FA%3E%26nbsp%3BI%20may%20be%20wrong%20but%20suspect%20this%20is%20not%20so%20easy%20with%20just%20a%20formula.%20However%2C%20when%20I%20take%20your%20example%20literally%20(i.e.%20compare%20%3CSTRONG%3Etwo%20cells%3C%2FSTRONG%3E%20with%20comma%20separated%20strings%20and%20display%20%22Error%22%20if%20duplicates%20are%20noticed)%2C%20perhaps%20the%20attached%20workbook%20contains%20an%20acceptable%20solution%20for%20you.%3C%2FP%3E%3CP%3EIt%20uses%20PowerQuery%20(PQ)%20and%20requires%20two%20named%20ranges%2C%20each%20pointing%20at%20one%20of%20the%20two%20cells.%20In%20my%20example%20I%20named%20them%20%22cellB26%22%20and%20cellD26%22.%20The%20query%20creates%20separate%20tables%20from%20each%20cell%20and%20checks%20for%20matching%20records%2C%20through%20merging%20(Inner%20join)%20these%20two%20tables%20.%20If%20there%20are%20matching%20records%2C%20then%20%22Error%22%20will%20be%20returned%20in%20cell%20F26.%20If%2C%20on%20the%20other%20hand%20the%20merged%20table%20is%20empty%20(i.e.%20no%20matching%20records%20found)%20then%20F26%20remains%20empty.%3C%2FP%3E%3CP%3EChange%20the%20content%20in%20cells%20B26%20and%2For%20D26%20and%20press%20%22Refresh%20All%22%20on%20the%20Data%20ribbon%20and%20see%20if%20it%20does%20what%20you%20expect.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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). It must not happen that a name appears in both cells. I would like to have a control function in column F using an Excel formula that warns if a name occurs in cells B and D.


I would be grateful for any help!

 

---German---

Hallo zusammen,

ich suche nach einer Lösung für folgendes Problem. Es gibt zwei Zellen (B26 & D26) in denen Namen stehen die durch Kommatar getrennt sind. Die Anzahl der Namen ist variabel (sollte 40 wahrscheinlich nicht überschreiten). Es darf nicht vorkommen, das ein Name in beiden Zellen vorkommt. Ich hätte gerne in Spalte F eine Kontrollfunktion per Excel-Formel, die warnt wenn ein Name in Zelle B und D vorkommt.

 

Danke!

 

Zeile
BCDEF
26NameA, NameB NameC, NameD, NameA, NameE 

=wenn(a Name in B26  = a  Name in D26; "Error!"; "")

 

PS: Unfortunately, my boss wants it to be displayed like this ... Unfortunately, the table cannot be modified.

2 Replies

@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.

@Riny_van_Eekelen 

Oops, I believe I answered on that, but my reply for some strange reason disappeared. Anyway, just in case another variant of PQ solution.