Comparing two cells with comma separated lists of values and returning any duplicates

[CellHell]

Easy one to explain, but frustrating to try and resolve!... 1 cell has '7,8,9', the next cell has '8,9,10' and I want it to compare and report the overlap '8,9'. I can't split these out into separate cells due to the spreadsheet size as each cell might contain upto 12 or so values and ideally I would be able to simultaneously compare 8 or so cells together with the same operation (although even comparing 2 with multiple values would be very useful). It would be ok to CONCAT all the values into one cell and then pull out the duplicates perhaps? ...If it also reported the number of overlapped ; so '2' for '8,9' that would be great but suspect I could find a way for that easier bit. Any help would be really greatly appreciated , to my mind this should be simple and quite a common comparison but lost on this one!....if I have to scrub up on VBA to resolve then so be it. Thanks for reading!

 

[JamesCanale]

This is a silly solution.

MrExcelPlayground2.xlsx
	K	L
1	5,6,7,8,9,10	8,9,10
2	8,9,10,11,12
Sheet4

Cell Formulas
Range	Formula
L1	L1	=TEXTJOIN(",",TRUE,XLOOKUP(VALUE(MID(K1,SORT(UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1))),LEN(K1)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1)))+1,1)),1,1)))),VALUE(MID(K2,SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2))),LEN(K2)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1)))),VALUE(MID(K2,SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2))),LEN(K2)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1)))),"",0))

 

[Fluff]

Hi & welcome to MrExcel.
A couple of options, the formula in C3 only works on a pc.

+Fluff 1.xlsm
	A	B	C
1
2	7,8,9,1,14,15		7,9,14,15,19,2
3	9,10,15,11		7,9,14,15,19,2
4	7,23,19,26,2,3
5	14,19,2
6
Main

Cell Formulas
Range	Formula
C2	C2	=LET(a,TEXTJOIN(",",,A2:A5),b,TRIM(MID(SUBSTITUTE(a,",",REPT(" ",100)),SEQUENCE(LEN(a)-LEN(SUBSTITUTE(a,",",""))+1,,,100),100)),TEXTJOIN(",",,UNIQUE(FILTER(b,ISNA(MATCH(b, UNIQUE(b,,1),0))))))
C3	C3	=TEXTJOIN(",",,FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A2:A5,",","</m><m>"))&"</m></k>","//m[.=following-sibling::m]"))

 

[CellHell]

This is a silly solution.

MrExcelPlayground2.xlsx
	K	L
1	5,6,7,8,9,10	8,9,10
2	8,9,10,11,12
Sheet4

Cell Formulas
Range	Formula
L1	L1	=TEXTJOIN(",",TRUE,XLOOKUP(VALUE(MID(K1,SORT(UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1))),LEN(K1)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K1,SEQUENCE(LEN(K1)))+1,1)),1,1)))),VALUE(MID(K2,SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2))),LEN(K2)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1)))),VALUE(MID(K2,SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1),UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2))),LEN(K2)+1))-(SORT(UNIQUE(IFERROR(FIND(",",K2,SEQUENCE(LEN(K2)))+1,1)),1,1)))),"",0))

thanks so much James - if i knew you I would be buying you a pint at this stage.

 

[JamesCanale]

thanks so much James - if i knew you I would be buying you a pint at this stage.

You are welcome. I'll help myself to a pint.

 

[CellHell]

Hi & welcome to MrExcel.
A couple of options, the formula in C3 only works on a pc.

+Fluff 1.xlsm
	A	B	C
1
2	7,8,9,1,14,15		7,9,14,15,19,2
3	9,10,15,11		7,9,14,15,19,2
4	7,23,19,26,2,3
5	14,19,2
6
Main

Cell Formulas
Range	Formula
C2	C2	=LET(a,TEXTJOIN(",",,A2:A5),b,TRIM(MID(SUBSTITUTE(a,",",REPT(" ",100)),SEQUENCE(LEN(a)-LEN(SUBSTITUTE(a,",",""))+1,,,100),100)),TEXTJOIN(",",,UNIQUE(FILTER(b,ISNA(MATCH(b, UNIQUE(b,,1),0))))))
C3	C3	=TEXTJOIN(",",,FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,SUBSTITUTE(A2:A5,",","</m><m>"))&"</m></k>","//m[.=following-sibling::m]"))

thanks Fluff - again I would be buying you a pint if I knew you. I will test this version out tomorrow and let you know how I get on. thanks again, much appreciated.

 

[Fluff]

Ok,

 

[CellHell]

Hi Fluff, sorry one last one.... the XML formula is working great apart from one added complication - If I want non-adjacent cells instead of a range e.g. 'A2,B2,E2' instead of 'A2:E2' is this constrained by this type of formula (using XML) or is it just that I need to do something with SUBSTITUTE (I have only ever used this for singular references before and cant find an example with multiple non-adjacent cells, so not sure if I have to do something nested perhaps?). Apologies for my ignorance - I have read up on XML since getting this formula, but I can't fathom why it won't allow. Any further guidance, greatly appreciated.

 

[CellHell]

Hi Fluff, sorry one last one.... the XML formula is working great apart from one added complication - If I want non-adjacent cells instead of a range e.g. 'A2,B2,E2' instead of 'A2:E2' is this constrained by this type of formula (using XML) or is it just that I need to do something with SUBSTITUTE (I have only ever used this for singular references before and cant find an example with multiple non-adjacent cells, so not sure if I have to do something nested perhaps?). Apologies for my ignorance - I have read up on XML since getting this formula, but I can't fathom why it won't allow. Any further guidance, greatly appreciated.

ignore - sorry.... I should have known this one. I just needed to type it out and I figured out what I was doing wrong....sorry! and thanks.

 

[Fluff]

Glad you sorted it & thanks for the feedback.