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]

Try VBA solution.
If you are new VBA user:
1) Alt-F11 to open VBA window
2) Insert/ Modules/Module 1
3) paste below code into edit window
4) Hit F5 (or "play" button in menu bar) to run the code

Option Explicit
Sub TEST()
Dim lr&, lc&, k&, i&, j&, t&, count&, cell As Range, report(), stock(), res1(), res2(1 To 100000, 1 To 5), res3()
With Sheets("REPORT")
    lr = .Cells(Rows.count, "B").End(xlUp).Row
    lc = Evaluate("=LOOKUP(2,1/(REPORT!1:1=""NET""),COLUMN(REPORT!1:1))")
    ReDim report(1 To lr - 1, 1 To 5)
    ReDim res3(1 To lr - 1, 1 To 5)
    For Each cell In .Range("B2:B" & lr)
        If Not IsEmpty(cell.Offset(0, 1)) Then
            k = k + 1
            report(k, 1) = WorksheetFunction.Trim(cell) & " " & cell.Offset(0, 1) & " " & cell.Offset(0, 2)
            report(k, 2) = cell.Offset(0, lc - 2)
            report(k, 3) = cell
            report(k, 4) = cell.Offset(0, 1)
            report(k, 5) = cell.Offset(0, 2)
         End If
    Next
End With
With Sheets("STOCK")
    lr = .Cells(Rows.count, "B").End(xlUp).Row
    ReDim stock(1 To lr - 1, 1 To 1)
    k = 0
    For Each cell In .Range("B2:B" & lr)
        k = k + 1
        stock(k, 1) = WorksheetFunction.Trim(cell)
    Next
ReDim res1(1 To UBound(stock), 1 To 5)
k = 0
For i = 1 To UBound(stock)
    For j = 1 To UBound(report)
        If stock(i, 1) = report(j, 1) Then
            k = k + 1
            res1(k, 1) = k
            res1(k, 2) = report(j, 3)
            res1(k, 3) = report(j, 4)
            res1(k, 4) = report(j, 5)
            res1(k, 5) = report(j, 2)
        End If
    Next
Next
For i = 1 To UBound(report)
    count = 0
    For j = 1 To UBound(stock)
        If report(i, 1) = stock(j, 1) Then
            count = count + 1
        End If
    Next
    If count = 0 And report(i, 1) <> "" Then
        t = t + 1
        k = k + 1
        res2(t, 1) = k
        res2(t, 2) = report(i, 3)
        res2(t, 3) = report(i, 4)
        res2(t, 4) = report(i, 5)
        res2(t, 5) = report(i, 2)
    End If
Next
.Range("E2:I1000000").ClearContents
.Range("E2").Resize(UBound(res1), 5).Value = res1
.Range("E2").Offset(UBound(res1), 0).Resize(UBound(res2), 5).Value = res2
End With
End Sub

 

[leap out]

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

 

[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

With few thousand records of data
"For Each cell..."
working with cell located in the sheet have no issue.
With quite larger data, variable array should be use to read/write from/to sheet
How large is your actual data?

 

[leap out]

How large is your actual data?

actually 1500 rows , but I'm surprised from slowness with dummy data as in OP

 

[bebo021999]

actualy rows =sheet1's rows * sheet2's rows (you can see few double loops in my code)
OK. Will come back tomorrow with array solution.

 

[leap out]

OK. Will come back tomorrow with array solution.

I truly appreciate if you can achieve it

 

[bebo021999]

Try again to see if its perfomance is better

Option Explicit
Sub TEST()
Dim lr&, lc&, k&, i&, j&, T&, count&, report(), stock(), res1(), res2(1 To 100000, 1 To 5), res3()
Dim rng
With Sheets("REPORT")
    lr = .Cells(Rows.count, "B").End(xlUp).Row
    lc = Evaluate("=LOOKUP(2,1/(REPORT!1:1=""NET""),COLUMN(REPORT!1:1))")
    ReDim report(1 To lr - 1, 1 To 5)
    ReDim res3(1 To lr - 1, 1 To 5)
    rng = .Range("B2", .Cells(lr, lc)).Value
    For i = 1 To lr - 1
        If rng(i, 2) <> "" Then
            k = k + 1
            report(k, 1) = WorksheetFunction.Trim(rng(i, 1)) & " " & rng(i, 2) & " " & rng(i, 3)
            report(k, 2) = rng(i, lc - 1)
            report(k, 3) = rng(i, 1)
            report(k, 4) = rng(i, 2)
            report(k, 5) = rng(i, 3)
         End If
    Next
End With
With Sheets("STOCK")
    lr = .Cells(Rows.count, "B").End(xlUp).Row
    ReDim stock(1 To lr - 1, 1 To 1)
    rng = .Range("B2:B" & lr).Value
    For i = 1 To lr - 1
            stock(i, 1) = WorksheetFunction.Trim(rng(i, 1))
    Next
ReDim res1(1 To UBound(stock), 1 To 5)
k = 0
For i = 1 To UBound(stock)
    For j = 1 To UBound(report)
        If stock(i, 1) = report(j, 1) Then
            k = k + 1
            res1(k, 1) = k
            res1(k, 2) = report(j, 3)
            res1(k, 3) = report(j, 4)
            res1(k, 4) = report(j, 5)
            res1(k, 5) = report(j, 2)
        End If
    Next
Next
For i = 1 To UBound(report)
    count = 0
    For j = 1 To UBound(stock)
        If report(i, 1) = stock(j, 1) Then
            count = count + 1
        End If
    Next
    If count = 0 And report(i, 1) <> "" Then
        T = T + 1
        k = k + 1
        res2(T, 1) = k
        res2(T, 2) = report(i, 3)
        res2(T, 3) = report(i, 4)
        res2(T, 4) = report(i, 5)
        res2(T, 5) = report(i, 2)
    End If
Next
.Range("E2:I1000000").ClearContents
.Range("E2").Resize(UBound(res1), 5).Value = res1
.Range("E2").Offset(UBound(res1), 0).Resize(UBound(res2), 5).Value = res2
End With
End Sub

 

[leap out]

thanks again
it doesn't make difference . the same speed

 

[leap out]

cross-posted
match & put the items for next to adjacent cells