autofill for 11,200 rows based on matching between two sheets with another

[Hasson]

good morning !
I want matching data between two sheets (sheet1,2) based on column B with sheet RES and fill the values from column H:L in sheet RES .
should brings the values from columns F for sheets 1,2 and put in columns H,I after that the column J=column G * column H and column K= column G* column I and column L= column J-column K . the data colud be more than 11000 rows across sheets

PUR1.xlsx
	A	B	C	D	E	F	G
1	ITEM	CODE	DESCIBE	PRODUCTION BY	QTY	UNIT PRICE	TOTAL
2	1/1/2021	ATR-A100	CLA1 23M-1	IT	200.000	200.000	40,000.000
3	1/2/2021	ATR-A101	CLA2 VBG L	CHI	300.000	300.000	90,000.000
4	1/3/2021	ATR-A102	CLA3	TAI	400.000	400.000	160,000.000
5	1/4/2021	ATR-A103	CLA4	TR	500.000	500.000	250,000.000
6	1/5/2021	ATR-A104	CLA5	EG	600.000	600.000	360,000.000
7	1/6/2021	ATR-A105	M230	TU	700.000	700.000	490,000.000
8	1/7/2021	ATR-A106	CLA7	US	800.000	800.000	640,000.000
9	1/8/2021	ATR-A107	CLA8	UK	900.000	900.000	810,000.000
10	1/9/2021	ATR-A108	CLA9 N	BR	1,000.000	1,000.000	1,000,000.000
11	1/10/2021	ATR-A109	CLA10	IT	1,100.000	1,100.000	1,210,000.000
12	1/11/2021	ATR-A110	LVD	CH	1,200.000	1,200.000	1,440,000.000
13	1/12/2021	ATR-A111	CLA12	JA	1,300.000	1,300.000	1,690,000.000
14	1/13/2021	ATR-A112	CLA13	TR	1,400.000	1,400.000	1,960,000.000
15	1/14/2021	ATR-A113	CLA14 SS230	EG	1,500.000	1,500.000	2,250,000.000
16	1/15/2021	ATR-A114	CLA15	TU	1,600.000	1,600.000	2,560,000.000
17	1/16/2021	ATR-A115	CLA16	US	1,700.000	1,700.000	2,890,000.000
18	1/17/2021	ATR-A116	CLA17	UK	1,800.000	1,800.000	3,240,000.000
19	1/18/2021	ATR-A117	RRSDF	BR	1,900.000	1,900.000	3,610,000.000
20	1/19/2021	ATR-A118	CLA19	IT	2,000.000	2,000.000	4,000,000.000
21	1/20/2021	ATR-A119	CLA20	CHI	2,100.000	2,100.000	4,410,000.000
22	1/21/2021	BBTR-A120	CLA21	IT	2,200.000	2,200.000	4,840,000.000
23	1/22/2021	BBTR-A121	CLA22	CHI	2,300.000	2,300.000	5,290,000.000
24	1/23/2021	BBTR-A122	CLA23	IT	2,400.000	2,400.000	5,760,000.000
25	1/24/2021	BBTR-A123	CLA24	CHI	2,500.000	2,500.000	6,250,000.000
26	1/25/2021	BBTR-A124	CLA25	IT	2,600.000	2,600.000	6,760,000.000
27	1/26/2021	BBTR-A125	CLA26	CHI	2,700.000	2,700.000	7,290,000.000
28	1/27/2021	BBTR-A126	CLA27	IT	2,800.000	2,800.000	7,840,000.000
29	1/28/2021	BBTR-A127	CLA28	CHI	2,900.000	2,900.000	8,410,000.000
30	1/29/2021	BBTR-A128	CLA29	IT	3,000.000	3,000.000	9,000,000.000
31	1/30/2021	BBTR-A129	CLA30	CHI	3,100.000	3,100.000	9,610,000.000
sheet1

Cell Formulas
Range	Formula
G2:G31	G2	=E2*F2

PUR1.xlsx
	A	B	C	D	E	F	G
1	ITEM	CODE	DESCIBE	PRODUCTION BY	QTY	UNIT PRICE	TOTAL
2	1/1/2021	ATR-A100	CLA1 23M-1	IT	200.000	220.000	44,000.000
3	1/2/2021	ATR-A101	CLA2 VBG L	CHI	300.000	330.000	99,000.000
4	1/3/2021	ATR-A102	CLA3	TAI	400.000	450.000	180,000.000
5	1/4/2021	ATR-A103	CLA4	TR	500.000	560.000	280,000.000
6	1/5/2021	ATR-A104	CLA5	EG	600.000	622.000	373,200.000
7	1/6/2021	ATR-A105	M230	TU	700.000	722.000	505,400.000
8	1/7/2021	ATR-A106	CLA7	US	800.000	870.000	696,000.000
9	1/8/2021	ATR-A107	CLA8	UK	900.000	920.000	828,000.000
10	1/9/2021	ATR-A108	CLA9 N	BR	1,000.000	900.000	900,000.000
11	1/10/2021	ATR-A109	CLA10	IT	1,100.000	860.000	946,000.000
12	1/11/2021	ATR-A110	LVD	CH	1,200.000	980.000	1,176,000.000
13	1/12/2021	ATR-A111	CLA12	JA	1,300.000	456.000	592,800.000
14	1/13/2021	ATR-A112	CLA13	TR	1,400.000	234.000	327,600.000
15	1/14/2021	ATR-A113	CLA14 SS230	EG	1,500.000	678.000	1,017,000.000
16	1/15/2021	ATR-A114	CLA15	TU	1,600.000	234.000	374,400.000
17	1/16/2021	ATR-A115	CLA16	US	1,700.000	1,200.000	2,040,000.000
18	1/17/2021	ATR-A116	CLA17	UK	1,800.000	1,300.000	2,340,000.000
19	1/18/2021	ATR-A117	RRSDF	BR	1,900.000	1,400.000	2,660,000.000
20	1/19/2021	ATR-A118	CLA19	IT	2,000.000	1,500.000	3,000,000.000
21	1/20/2021	ATR-A119	CLA20	CHI	2,100.000	1,666.000	3,498,600.000
SHEET2

Cell Formulas
Range	Formula
G2:G21	G2	=E2*F2

should be result

PUR1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	CODE	DESCIBE	PRODUCTION BY	QTYST	QTNM	QTY	UNIT PRICE	UNIT PRICE1	TOTAL	TOTAL1	BALANCE
2	ATR-A107	CLA8	UK	2,099.000	123.000	900.000	900.000	900.000	810,000.000	810,000.000	0.000
3	ATR-A101	CLA2 VBG L	CHI	300.000	100.000	300.000	900.000	300.000	270,000.000	90,000.000	180,000.000
4	ATR-A102	CLA3	TAI	120.000	120.000	400.000	900.000	400.000	360,000.000	160,000.000	200,000.000
5	ATR-A100	CLA1 23M-1	IT	100.000	120.000	200.000	900.000	200.000	180,000.000	40,000.000	140,000.000
6	ATR-A103	CLA4	TR	20.000	122.000	500.000	900.000	500.000	450,000.000	250,000.000	200,000.000
7	ATR-A104	CLA5	EG	20.000	11.000	600.000	900.000	600.000	540,000.000	360,000.000	180,000.000
8	ATR-A105	M230	TU	20.000	12.000	700.000	900.000	700.000	630,000.000	490,000.000	140,000.000
9	ATR-A106	CLA7	US	20.000	11.000	800.000	900.000	800.000	720,000.000	640,000.000	80,000.000
RES

Cell Formulas
Range	Formula
J2:J9	J2	=G2*H2
K2:K9	K2	=I2*G2
L2:L9	L2	=J2-K2

 

[bebo021999]

Try below VBA code;
Hit Alt-F11 to open VBA edit window, insert/module, paste below code then hit F5 to run:

Option Explicit
Sub add()
Dim i&, j&, s1, s2, res
With Sheets("Sheet1")
    s1 = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheets("Sheet2")
    s2 = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheets("RES")
    res = .Range("B2:L" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(res)
    For j = 1 To UBound(s1)
        If res(i, 1) = s1(j, 2) Then
            res(i, 8) = s1(j, 6)
            res(i, 10) = res(i, 6) * res(i, 8)
            Exit For
        End If
    Next
    For j = 1 To UBound(s2)
        If res(i, 1) = s2(j, 2) Then
            res(i, 7) = s2(j, 6)
            res(i, 9) = res(i, 6) * res(i, 7)
            res(i, 11) = res(i, 9) - res(i, 10)
            Exit For
        End If
    Next
Next
.Range("H2:L1000000").ClearContents
.Range("B2").Resize(UBound(res), UBound(res, 2)).Value = res
End With
End Sub

 

[Hasson]

thanks . it shows a problem as highlighted in two last rows
somtimes I have data is in sheet1 but are not existed in sheet2 as in example BBTR-A129 , data are in sheet2 but not existed in sheet1 as example ATR-A120 .
this is what I got

PUR1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	ITEM	CODE	DESCIBE	PRODUCTION BY	QTYST	QTNM	QTY	UNIT PRICE	UNIT PRICE1	TOTAL	TOTAL1	BALANCE
2	1/1/2021	ATR-A107	CLA8	UK	2,099.000	123.000	900.000	920.000	900.000	828,000.000	810,000.000	18,000.000
3	1/2/2021	ATR-A101	CLA2 VBG L	CHI	300.000	100.000	300.000	330.000	300.000	99,000.000	90,000.000	9,000.000
4	1/3/2021	ATR-A102	CLA3	TAI	120.000	120.000	400.000	450.000	400.000	180,000.000	160,000.000	20,000.000
5	1/4/2021	ATR-A100	CLA1 23M-1	IT	100.000	120.000	200.000	220.000	200.000	44,000.000	40,000.000	4,000.000
6	1/5/2021	ATR-A103	CLA4	TR	20.000	122.000	500.000	560.000	500.000	280,000.000	250,000.000	30,000.000
7	1/6/2021	ATR-A104	CLA5	EG	20.000	11.000	600.000	622.000	600.000	373,200.000	360,000.000	13,200.000
8	1/7/2021	ATR-A105	M230	TU	20.000	12.000	700.000	722.000	700.000	505,400.000	490,000.000	15,400.000
9	1/8/2021	ATR-A106	CLA7	US	20.000	11.000	800.000	870.000	800.000	696,000.000	640,000.000	56,000.000
10	1/30/2021	BBTR-A129	CLA30	CHI	20.000	11.000	200.00		3,100.00		620,000.00
11	1/21/2021	ATR-A120	CLA21	CHI	20.000	11.000	100.00	1,666.00		166,600.00		166,600.00
RES

but the right

PUR1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	ITEM	CODE	DESCIBE	PRODUCTION BY	QTYST	QTNM	QTY	UNIT PRICE	UNIT PRICE1	TOTAL	TOTAL1	BALANCE
2	1/1/2021	ATR-A107	CLA8	UK	2,099.000	123.000	900.000	920.000	900.000	828,000.000	810,000.000	18,000.000
3	1/2/2021	ATR-A101	CLA2 VBG L	CHI	300.000	100.000	300.000	330.000	300.000	99,000.000	90,000.000	9,000.000
4	1/3/2021	ATR-A102	CLA3	TAI	120.000	120.000	400.000	450.000	400.000	180,000.000	160,000.000	20,000.000
5	1/4/2021	ATR-A100	CLA1 23M-1	IT	100.000	120.000	200.000	220.000	200.000	44,000.000	40,000.000	4,000.000
6	1/5/2021	ATR-A103	CLA4	TR	20.000	122.000	500.000	560.000	500.000	280,000.000	250,000.000	30,000.000
7	1/6/2021	ATR-A104	CLA5	EG	20.000	11.000	600.000	622.000	600.000	373,200.000	360,000.000	13,200.000
8	1/7/2021	ATR-A105	M230	TU	20.000	12.000	700.000	722.000	700.000	505,400.000	490,000.000	15,400.000
9	1/8/2021	ATR-A106	CLA7	US	20.000	11.000	800.000	870.000	800.000	696,000.000	640,000.000	56,000.000
10	1/30/2021	BBTR-A129	CLA30	CHI	20.000	11.000	200.00	3,100.00	-	620,000.00	-	620,000.00
11	1/21/2021	ATR-A120	CLA21	CHI	20.000	11.000	100.00	-	1,666.00	-	166,600.00	166,600.00
RES

you can see some values are not right in the right location for each column.
last thing if it's possible show empty cell zero but should replace by hyphen as show in second picture.

 

[bebo021999]

Could you share the actual file, with google.drive?

 

[Hasson]

ok here is the file
PUR.xlsm

 

[bebo021999]

For the first 8 lines:
Unit price: from sheet2
Unit price1: from sheet1

But from the last 2 lines, your manual expected outcome
Unit price: from sheet1
Unit price1: from sheet2

Why?

 

[Hasson]

sorry !! this is my mistake
it should
sheet1=unitprice
sheet2=unitprice1

 

[bebo021999]

Try again:

Option Explicit
Sub add()
Dim i&, j&, s1, s2, res
With Sheets("Sheet1")
    s1 = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheets("Sheet2")
    s2 = .Range("A2:F" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
End With
With Sheets("RES")
    res = .Range("B2:L" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(res)
    For j = 1 To UBound(s1)
        If res(i, 1) = s1(j, 2) Then
            res(i, 7) = s1(j, 6)
            res(i, 9) = res(i, 6) * res(i, 7)
            Exit For
        End If
    Next
    For j = 1 To UBound(s2)
        If res(i, 1) = s2(j, 2) Then
            res(i, 8) = s2(j, 6)
            res(i, 10) = res(i, 6) * res(i, 8)
            Exit For
        End If
    Next
Next
.Range("H2:L1000000").ClearContents
.Range("B2").Resize(UBound(res), UBound(res, 2)).Value = res
.Range("L2").Resize(UBound(res), 1).Formula = "=K2-J2"
End With
End Sub

 

[Hasson]

thanks very much for your time and help