Compares comma separated strings of information and returns the result if the data is the same

[thehoang]

Hello MrExcel.com
I haven't been back to the forum for a long time.

Currently, I have a problem, I present it as below.

I have 1 Sheet "RawData" and 1 Sheet "DataBase".

Sheet_RawData: B1:M8, this is the data I update daily on the production line.

Sheet_DataBase: A1:M35, this data will include a lot, each Finished Material in column B is a program.

Currently, I need to get data into column M of Sheet_RawData.

and the information to compare is column E and column J Sheet_RawData with column B and column I Sheet_DataBase.

There is a problem here, that is, the data in column J sheet_RawData will have 1 or several data and are connected by commas.

I wish there was a formula that could check all the values in the cells in column J separated by commas and compare them with column I Sheet_DataBase. If there are duplicate data values, it will return the content "Part Number" in column M sheet_RawData.

MrExcel Support.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Side	Finished Material	Sub-Assy Material	PCB Name	Series Number	F/R P.	Part Number	Old Part Number	Positions	Description	Feeder Type	Comp.t pitch	Qty.
2		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 22	IN60000001468	10002477	R25	06R249K	8mm	4mm(4*1)	1
3		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 23	IN60000003757	14-1662-492	R59	06R24.9K	8mm	4mm(4*1)	1
4		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 24	IN60000001177	14-1662-152	D14	Diode	8mm	4mm(4*1)	1
5		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 25	IN60000035953	14-5235-332	D3	Diode	8mm	4mm(4*1)	2
6		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 26	IN60000023970	14-1664-992	R46,R99	06R49.9K	8mm	4mm(4*1)	2
7		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 27	IN60000016711	14-1666-811	R42,R83	06R6.81K	8mm	4mm(4*1)	2
8		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 28	IN60000031305	14-5225-102	C36,C45	08C1000P	8mm	4mm(4*1)	2
9		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 29	IN60000011483	14-1651-0R0	R116,R117	08R10R	8mm	4mm(4*1)	2
10		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 30	IN60000032957	10002638	R114,R115	08R24.9R	8mm	4mm(4*1)	2
11		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 31	IN60000035290	10005081	C61	08C4.64P	8mm	4mm(4*1)	2
12		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 32	IN60000037573	10001461	R73,R74	08R75R	8mm	4mm(4*1)	2
13		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 33	IN60000002772	10002902	R19	06R2.67K	8mm	4mm(4*1)	1
14		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 34	IN60000036912	10002253	R96	06R1M	8mm	4mm(4*1)	1
15		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 35	IN60000001822	14-1661-243	R23	06R124K	8mm	4mm(4*1)	1
16		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 36	IN60000035939	14-1661-102	R27	06R11K	8mm	4mm(4*1)	1
17		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 37	IN60000024649	14-1661-103	R86	06R110K	8mm	4mm(4*1)	1
18		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 38	IN60000025283	14-1661-053	R69	06R105K	8mm	4mm(4*1)	1
19		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 39	IN60000002476	14-1660-0R0	R110	06R0R	8mm	4mm(4*1)	1
20		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 40	IN60000047008	10015610	C28	06C1U	8mm	4mm(4*1)	1
21		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 41	IN60000011500	14-5235-221	C16,C20	06C220P	8mm	4mm(4*1)	2
22		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 42	IN60000020659	14-1662-002	R67,R89,R97,R98	06R20K	8mm	4mm(4*1)	4
23		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 43	IN60000036632	14-1664-991	R62,R106,R108,R118	06R4.99K	8mm	4mm(4*1)	4
24		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 44	IN60000035951	14-5225-104	C59,C85,C86,C87	08C0.1U	8mm	4mm(4*1)	4
25		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 45	IN60000025280	14-1661-002	R20,R56,R58,R65,R70,R71,R104	06R10K	8mm	4mm(4*1)	7
26		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 46	IN60000037258	14-1661-001	R57,R63,R72,R88,R91,R102,R107,R113	06R1K	8mm	4mm(4*1)	8
27		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 47	IN60000046538	10004138	C44,C47,C69,C70,C71,C72,C73,C74,C75	06C0.1U	8mm	4mm(4*1)	9
28		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 48	IN60000026659	14-1662-001	R40,R43,R45,R55,R60,R66,R75,R92,R93,R94	06R2K	8mm	4mm(4*1)	10
29		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 49	IN60000022954	10004137	C34,C49,C52,C53,C54,C55,C57,C76,C77,C78,C79,C80,C88,C94	06C0.01U	8mm	4mm(4*1)	14
30		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 50	IN60000008845	14-1661-003	R26,R68,R90,R95	06R100K	8mm	4mm(4*1)	4
31		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 51	IN60000027316	14-5225-103	C43,C51,C58	08C.01U	8mm	4mm(4*1)	3
32		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 52	IN60000040843	10000624	R50,R54,R61	06R3.01K	8mm	4mm(4*1)	3
33		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 53	IN60000012105	14-1661-0R0	R37,R38,R81	06R10R	8mm	4mm(4*1)	3
34		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 54	IN60000019365	14-1661-000	R52,R53,R101	06R100R	8mm	4mm(4*1)	3
35		MFA350PS24-XT1175	SF10000013247-B	IN60000013722-C	MFA350PS24	F- 55	IN60000011501	14-5235-471	C18,C24,C27	06C470P	8mm	4mm(4*1)	3
DataBase

MrExcel Support.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Barcode	Defect Code	Problem Category	Finished Material	Series	Date	Check	Line	Positions Error	Positions	Count	Part Number
2		V24520054	SM12	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	R81			IN60000012105
3		V24520020	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	D22,C79			IN60000022954
4		V24520019	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	C18,C53			IN60000011501
5		V24520009	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	C18,C24			IN60000011501
6		V24520015	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	D14			IN60000001177
7		V24520016	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	D30,D3			IN60000035953
8		V24520007	PS03	Non - Wetting	MFA350PS24-XT1175	MFA350	14-Jan-25	T. NGOC	SMT2	C18,C61			IN60000035290
RawData

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1	Cell Value	contains " "	text	NO

Hope to get some review and support for this article.
Thanks!