put the values multiple columns into adjacent value based on match another column

[leap out]

Hi Guys
I hope finding way to brings data from sheet report . so should match column B:D for sheet REPORT with column B for sheet STOCK , then should put the data into E:I for each adjacent cell into column B after match column B:D for sheet REPORT and if there are items are existed in sheet REPORT but are not existed in column B for sheet STOCK , then should be added into E: I as shows in result . as to the values in column I should brings from the last column NET . the last column NET (M) is not the last column. I will insert new columns then will change the column NET in location (this means the column M is not range end when brings the values if I insert new column after it ) .

sheet REPORT

(5).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	CLASSIFICATION	GOODS	MARK	MANFACTURE	BUYING	SELLING	NET	BUYING	SELLING	NET	BUYING	SELLING	NET
2	OIL-AS2	MLO-1 10W40 208L TT/L	CAS	SU	200		200	200	40	360	-	2	358
3		10W40 208L	ENI	IT	300		300	400	20	680	400	20	1060
4		5W30 208L	Q8	EU	120		120	60	20	160	60	-	220
5		10W40 208L	Q8	EU	100		100			100	-	100	-
6		TOTAL			720	0	720	660	80	1300	-	-	1638
7	OIL-AS2	10W40 12x1L	Q8	EU	123		123	100	10	213	-	10	203
8		10W40 12x1L	CAS	SU	30		30	600	5	625	600	5	1220
9		10W40 12x1L	ENI	IT	120		120	125		245	125	-	370
10		OL-115W40 12x1L	CAS	SU**2	0		0	131		131	-	131	-
11		TOTAL			273	0	273	956	15	1214	725	146	1793
12	MS-OIL1	5W30 12x1L	Q8	EU	200		200	100		300	100	-	400
13		10W40 4x4L	Q8	EU	300		300			300	-	-	300
14		OIL/M 10W40 4x4L	CAS TRM	SU N3-90	230		230			230	-	-	230
15		10W40 4x4L MNH/80-T	ENI	IT L66RT	123		123	125	125	123	125	125	123
16		5W40 4x4L	Q8	EU	456		456			456	-	-	456
17		5W40 4x4L	CAS	SU	234		234			234	-	-	234
18		5W40 4x4L	ENI	IT	230		230	20	20	230	20	-	250
19		20W50 4x4L	Q8	EU	123		123	10		133	10	-	143
20		20W50 4x4L	CAS	SU	560		560		12	548	-	12	536
21		20W50 4x4L	ENI	IT	120		120			120	-	-	120
22		5W30 4x4L	Q8	EU	30		30			30	-	-	30
23		TOTAL			2606	0	2606	255	157	2704	255	137	2822
REPORT

Cell Formulas
Range	Formula
M6,E11:M11,E6:J6	E6	=SUM(E2:E5)
G12:G22,G7:G10,G2:G5	G2	=E2-F2
M12:M22,M7:M10,M2:M5,J12:J22,J7:J10,J2:J5	J2	=G2+H2-I2
E23:M23	E23	=SUM(E12:E22)

sheet STOCK

(5).xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	GOODS	QTY		ITEM	GOODS	MARK	MANFACTURE	NET
2	1	10W40 12x1L Q8 EU	200
3	2	10W40 12x1L CAS SU	120
4	3	10W40 12x1L ENI IT	300
5	4	OL-115W40 12x1L CAS SU**2	240
6	5	MLO-1 10W40 208L TT/L CAS SU	150
7	6	10W40 208L ENI IT	160
8	7	5W30 208L Q8 EU	200
9	8	OIL/M 10W40 4x4L CAS TRM SU N3-90	120
10
11
12
13
14
15
16
17
18
19
20
STOCK

after

(5).xlsx
	A	B	C	D	E	F	G	H	I
1	ITEM	GOODS	QTY		ITEM	GOODS	MARK	MANFACTURE	NET
2	1	10W40 12x1L Q8 EU	200		1	10W40 12x1L	Q8	EU	203
3	2	10W40 12x1L CAS SU	120		2	10W40 12x1L	CAS	SU	1220
4	3	10W40 12x1L ENI IT	300		3	10W40 12x1L	ENI	IT	370
5	4	OL-115W40 12x1L CAS SU**2	240		4	OL-115W40 12x1L	CAS	SU**2	-
6	5	MLO-1 10W40 208L TT/L CAS SU	150		5	MLO-1 10W40 208L TT/L	CAS	SU	358
7	6	10W40 208L ENI IT	160		6	10W40 208L	ENI	IT	1060
8	7	5W30 208L Q8 EU	200		7	5W30 208L	Q8	EU	220
9	8	OIL/M 10W40 4x4L CAS TRM SU N3-90	120		8	OIL/M 10W40 4x4L	CAS TRM	SU N3-90	230
10					9	10W40 208L	Q8	EU	-
11					10	5W30 12x1L	Q8	EU	400
12					11	10W40 4x4L	Q8	EU	300
13					12	10W40 4x4L MNH/80-T	ENI	IT L66RT	123
14					13	5W40 4x4L	Q8	EU	456
15					14	5W40 4x4L	CAS	SU	234
16					15	5W40 4x4L	ENI	IT	250
17					16	20W50 4x4L	Q8	EU	143
18					17	20W50 4x4L	CAS	SU	536
19					18	20W50 4x4L	ENI	IT	120
20					19	5W30 4x4L	Q8	EU	30
STOCK

 

[bebo021999]

great ! this is exactly what I want. but I have question why the code seems slow. it gives running speed 0.940 I thinks this much time with comparison small data if you can make it 0.02 or 0.03 will be great and I truly appreciate for that

Try to test again with your actual data. Insert more dummy data.
How long does it take ?
If not, try to send file via google drive.
I had tried my best to use variable array to reduce the code speed.
Hope someone can help you out.

 

[leap out]

here is the file
RP.xlsm

 

[bebo021999]

I tested with 10000 rows in sheet report and it took 0.57s.
Is it acceptable?

 

[leap out]

Is it acceptable?

surly will be great if you can achieve it .

 

[bebo021999]

surly will be great if you can achieve it .

I did and I got it.
Have you tried it ?

 

[leap out]

I no know what happens . actually after many tries it gives 0.140 . this is very acceptable

but I have question if I have less than 10000 rows . for instance 1000 rows code runs loop throught 10000 rows even they are empty rows .

if so I think this is the reason make the code is slow if the data are small I think the loop should be throught the rows contain data without pass the others empty rows . what do you think to avoid this problem ?

 

[bebo021999]

With this:
lr = .Cells(Rows.count, "B").End(xlUp).Row
code works for range from first row to last row of column B only.

 

[leap out]

ok thanks for clarification .
many thanks for your time & assistance