Formula required to completed the below condition

[Achyuth]

Hello Everyone,

I have a query below with these condition, requesting to please help me solve this.

QA Tag	Mod Tag	Difference
0c103e02,9961fd48,a4b63c62,a7eb2d76,d044fd17,ea1bea7e	725f5ee4,a7eb2d76	0c103e02
0c103e02,9961fd48,a4b63c62,a7eb2d76,cafa8afb,d044fd17,ea1bea7e	9961fd48,a4b63c62	0c103e02
18915865	none	18915865
344922fa	71a81f31	344922fa
4f9ff4d8	none	4f9ff4d8
6782f693	none	6782f693
6782f693	none	6782f693
6782f693	none	6782f693
6782f693	none	6782f693
725f5ee4,df494a74,f7205075	df494a74,f7205075	725f5ee4
809d1041	a7eb2d76	809d1041
d8d0d525	none	d8d0d525

I have used this formual which returns only one value which is not there, is there a way to return all the values present, I mean up to 10 missing values.

Please help

Formula I have used is

=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),","&T111&",")),MID(","&S111&",",ROW(INDIRECT("1:"&LEN(S111)+1)),FIND(",",","&S111&",",ROW(INDIRECT("2:"&LEN(S111)+2)))-ROW(INDIRECT("1:"&LEN(S111)+1))+1),""),"")),","," "))," ",",")

 

[Achyuth]

Below is revised data set.

Data 1	Data 2	Output	Name
1,2,3,4,5	1,2	3,4,5	Person 1
1,3,4	6,7	1,3,4	Person 2
1,3,4,5,8	5,10	1,3,4,8	Person 3
2,5,6,7	1,3,4,6,8	2,5,7	Person 4
6,8,9,0	6,9,0	8	Person 1
1,4,5,6,7,0	1,3,5,6	4,7,0	Person 2
3,5,7,9	5,7,9	3	Person 3
2,6,8,0	2,5	6,8,0	Person 4
1,4,5,7,2	4,7,2	1,5	Person 1
9,7,5,4,1	9,7,1	5,4	Person 2
3,4,5,6,7	3,6,7	4,5	Person 3
3,6,8,0,1	3,0,1	6,8	Person 4
1,4,5	1,4	5	Person 1
1,2	2	1	Person 2
5,6,7	6,7	5	Person 3
4,8,0	8,0	4	Person 4

Formula used : =SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),1)=",",IF(ISERROR(SEARCH(MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),","&B2&",")),MID(","&A2&",",ROW(INDIRECT("1:"&LEN(A2)+1)),FIND(",",","&A2&",",ROW(INDIRECT("2:"&LEN(A2)+2)))-ROW(INDIRECT("1:"&LEN(A2)+1))+1),""),"")),","," "))," ",",")

Desired output which I am trying to achieve now using pivot table is below:

Name	Output Occurrence
Person 1	1,3,4,5,8
Person 2	1,3,4,5,7,0
Person 3	1,3,4,5,8
Person 4	2,4,5,6,7,8,0

Thank you so much for the earlier solution

 

[Peter_SSs]

Why are you continuing to use that very long & volatile formula when a shorter, non-volatile one is available?

This is how I would be approaching it

Achyuth.xlsm
	A	B	C	D	E	F	G
1	Data 1	Data 2	Output	Name		Name	Outpuut Occurrence
2	1,2,3,4,5	1,2	3,4,5	Person 1		Person 1	1,3,4,5,8
3	1,3,4	6,7	1,3,4	Person 2		Person 2	0,1,3,4,5,7
4	1,3,4,5,8	5,10	1,3,4,8	Person 3		Person 3	1,3,4,5,8
5	2,5,6,7	1,3,4,6,8	2,5,7	Person 4		Person 4	0,2,4,5,6,7,8
6	6,8,9,0	6,9,0	8	Person 1
7	1,4,5,6,7,0	1,3,5,6	4,7,0	Person 2
8	3,5,7,9	5,7,9	3	Person 3
9	2,6,8,0	2,5	6,8,0	Person 4
10	1,4,5,7,2	4,7,2	1,5	Person 1
11	9,7,5,4,1	9,7,1	5,4	Person 2
12	3,4,5,6,7	3,6,7	4,5	Person 3
13	3,6,8,0,1	3,0,1	6,8	Person 4
14	1,4,5	1,4	5	Person 1
15	1,2	2	1	Person 2
16	5,6,7	6,7	5	Person 3
17	4,8,0	8,0	4	Person 4
Sheet3

Cell Formulas
Range	Formula
F2:F5	F2	=UNIQUE(D2:D17)
G2:G5	G2	=TEXTJOIN(",",1,SORT(UNIQUE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(",",1,FILTER(C$2:C$17,D$2:D$17=F2)),",","</c><c>")&"</c></p>","//c"))))
C2:C17	C2	=LET(f,FILTERXML("<p><c>"&SUBSTITUTE(A2,",","</c><c>")&"</c></p>","//c"),TEXTJOIN(",",1,IF(ISERROR(FIND(","&f&",",","&B2&",")),f,"")))
Dynamic array formulas.