split column into multiple files into multiple columns based on another file

[Hasson]

hi
I srearched too much in this forum and the others , but I don't find like my question . so I would split column B into files PUR1,PUR2 into multiple columns into column B,CD,E based on splited data file SEARCH into columns B,C,D . the rsult should be in first sheet into files PUR1,2 and should replace the data every time run the macro . I put the expected result in both the files in sheet result . I have about five files in the same folder whit the same structure as in PUR1,2
also I issued in this forum split column into multiple files into multiple columns based on another file

file 1(pur1)

PUR1.xlsx
	A	B	C	D	E
1	ITEM	ID	BARCODE	WHARE HOUSE	QTY
2	1	QQW-1 MM CLA1 23M-1 IT			200.000
3	2	QQW-2 TH NM-1 CLA2 VBG L CHI			300.000
4	3	QQW-3 CV CLA3 TA			400.000
5	4	QQW-4 M*12.5 CLA4 TR			500.000
6	5	QQW-5 CLA5 EG			600.000
7	6	QQW-6 M230 TU			700.000
8	7	QQW-7 S** CLA7 US			800.000
9	8	QQW-8 CLA8 UK			900.000
10	9	QQW-9 CLA9 N BR			1,000.000
11	10	QQW-10 BN CLA10 IT			1,100.000
12	11	QQW-11 LVD CH			1,200.000
13	12	QQW-12 CLA12 JA			1,300.000
14	13	QQW-13 CLA13 TR			1,400.000
15	14	QQW-14 L/R CLA14 SS230 EG			1,500.000
16	15	QQW-15 CLA15 TU			1,600.000
17	16	QQW-16 CLA16 US			1,700.000
18	17	QQW-17 CLA17 UK			1,800.000
19	18	QQW-18 RRSDF BR			1,900.000
20	19	QQW-19 CLA19 IT			2,000.000
21	20	QQW-20 KV/1**2 CLA20 CHI			2,100.000
sheet1

file2 (pur2)

PUR2.xlsx
	A	B	C	D	E
1	ITEM	ID	BARCODE	WHARE HOUSE	QTY
2	1	QQW-14 L/R CLA14 SS230 EG			350.000
3	2	QQW-15 CLA15 TU			600.000
4	3	QQW-16 CLA16 US			150.000
5	4	QQW-17 CLA17 UK			200.000
6	5	QQW-18 RRSDF BR			300.000
7	6	QQW-19 CLA19 IT			400.000
8	7	QQW-20 KV/1**2 CLA20 CHI			500.000
REPORT

file3 search

SEARCH.xlsm
	A	B	C	D
1	ITEM	ID	DESCIBE	PRODUCTION BY
2	1	QQW-1 MM	CLA1 23M-1	IT
3	2	QQW-2 TH NM-1	CLA2 VBG L	CHI
4	3	QQW-3 CV	CLA3	TAI
5	4	QQW-4 M*12.5	CLA4	TR
6	5	QQW-5	CLA5	EG
7	6	QQW-6	M230	TU
8	7	QQW-7 S**	CLA7	US
9	8	QQW-8	CLA8	UK
10	9	QQW-9	CLA9 N	BR
11	10	QQW-10 BN	CLA10	IT
12	11	QQW-11	LVD	CH
13	12	QQW-12	CLA12	JA
14	13	QQW-13	CLA13	TR
15	14	QQW-14 L/R	CLA14 SS230	EG
16	15	QQW-15	CLA15	TU
17	16	QQW-16	CLA16	US
18	17	QQW-17	CLA17	UK
19	18	QQW-18	RRSDF	BR
20	19	QQW-19	CLA19	IT
21	20	QQW-20 KV/1**2	CLA20	CHI
RP

expected result based on file search
PUR1

PUR1.xlsx
	A	B	C	D	E
1	ITEM	ID	DESCIBE	PRODUCTION BY	QTY
2	1	QQW-1 MM	CLA1 23M-1	IT	200.000
3	2	QQW-2 TH NM-1	CLA2 VBG L	CHI	300.000
4	3	QQW-3 CV	CLA3	TAI	400.000
5	4	QQW-4 M*12.5	CLA4	TR	500.000
6	5	QQW-5	CLA5	EG	600.000
7	6	QQW-6	M230	TU	700.000
8	7	QQW-7 S**	CLA7	US	800.000
9	8	QQW-8	CLA8	UK	900.000
10	9	QQW-9	CLA9 N	BR	1,000.000
11	10	QQW-10 BN	CLA10	IT	1,100.000
12	11	QQW-11	LVD	CH	1,200.000
13	12	QQW-12	CLA12	JA	1,300.000
14	13	QQW-13	CLA13	TR	1,400.000
15	14	QQW-14 L/R	CLA14 SS230	EG	1,500.000
16	15	QQW-15	CLA15	TU	1,600.000
17	16	QQW-16	CLA16	US	1,700.000
18	17	QQW-17	CLA17	UK	1,800.000
19	18	QQW-18	RRSDF	BR	1,900.000
20	19	QQW-19	CLA19	IT	2,000.000
21	20	QQW-20 KV/1**2	CLA20	CHI	2,100.000
result

PUR2

PUR2.xlsx
	A	B	C	D	E
1	ITEM	ID	DESCIBE	PRODUCTION BY	QTY
2	1	QQW-14 L/R	CLA14 SS230	EG	350.000
3	2	QQW-15	CLA15	TU	600.000
4	3	QQW-16	CLA16	US	150.000
5	4	QQW-17	CLA17	UK	200.000
6	5	QQW-18	RRSDF	BR	300.000
7	6	QQW-19	CLA19	IT	400.000
8	7	QQW-20 KV/1**2	CLA20	CHI	500.000
result

thanks in advance

 

[Hasson]

for more detailes when split column B into files PUR1,PUR2 into multiple columns into column B,C,D,E based on file search should match the whole item into column B in two files PUR1,2 with the whole splited item into column B,C,D into file search .