match data based on three columns together and put the values into last empty columns

[KalilMe]

hi
I have two sheets should match sheet2 with sheet1 based on columns B,C,D toghether and copy the values to last two empty columns contain headers (purchase and sales) .so in this case after match the data between two sheets it will populate the values in COL E,F in sheet2 and if I run macro again will copy to next empty columns PURCHASE , SALES (H,I) an so on . every time run the macro should search for empty columns(PURCHASE , SALES) and populate the values based on what is existed in sheet1.
I put the expected result should be in sheet2 after matching with sheet1 . with considering the data in sheet1 are increasable and the inserted columns also are increasable in sheet2

sheet1

pop.xlsm
	A	B	C	D	E	F
1	DATE	COMMIDETY	TYPE	ORIGIN	PURCHASE	SALES
2	1/7/2021	FR	BANANA	SO	55	5
3	1/8/2021	FR	BANANA	SO1	10
4	1/10/2021	VEG	TOMATO	EG	5
5	1/17/2021	FO1	TUNE160G	SP	22	5
6	1/18/2021	FO1	TUNE160G	PO	10	10
7	1/18/2021	FO1	TUNE160G	SPL	5	-
SHEET1

sheet2 before

pop.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ITEM	COMMIDETY	TYPE	ORIGIN	PURCHASE	SALES	BALANCE	PURCHASE	SALES	BALANCE	PURCHASE	SALES	BALANCE	PURCHASE	SALES	BALANCE
2	1	FR	BANANA	SO			0			0			0			0
3	2	FR	BANANA	SO1			0			0			0			0
4	3	FO1	TUNE160G	SP			0			0			0			0
5	4	FO1	TUNE160G	PO			0			0			0			0
6	5	VEG	TOMATO	EG			0			0			0			0
7	6	FO1	TUNE160G	SPL			0			0			0			0
SHEET2

Cell Formulas
Range	Formula
P2:P7,M2:M7,J2:J7,G2:G7	G2	=E2-F2

sheet2 after

pop.xlsm
	A	B	C	D	E	F
1	ITEM	COMMIDETY	TYPE	ORIGIN	PURCHASE	SALES
2	1	FR	BANANA	SO	55	5
3	2	FR	BANANA	SO1	10
4	3	FO1	TUNE160G	SP	22	5
5	4	FO1	TUNE160G	PO	10	10
6	5	VEG	TOMATO	EG	5
7	6	FO1	TUNE160G	SPL	5
EXPECTED RESULT

 

[maabadi]

Then Try this:

Sub Macro2()
Dim Lr1 As Long, Lr2 As Long, Lc As Long, j As Long
Lr1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Lc = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
If Range("E2").Value = "" Then
Range("E2:F" & Lr2).Formula = "=INDEX(Sheet1!E$2:E$" & Lr2 & " ,MATCH(1,INDEX((Sheet1!$B$2:$B$" & Lr2 _
& " =$B2)*(Sheet1!$C$2:$C$" & Lr2 & " =$C2)*(Sheet1!$D$2:$D$" & Lr2 & " =$D2),0,1),0),1)"
Range("E2:F" & Lr2).Value = Range("E2:F" & Lr2).Value

Else
For j = 8 To Lc Step 3
If Cells(2, j).Value = "" Then
Range(Cells(2, j), Cells(Lr2, j + 1)).Formula = "=INDEX(Sheet1!E$2:E$" & Lr2 & " ,MATCH(1,INDEX((Sheet1!$B$2:$B$" & Lr2 _
& " =$B2)*(Sheet1!$C$2:$C$" & Lr2 & " =$C2)*(Sheet1!$D$2:$D$" & Lr2 & " =$D2),0,1),0),1)"
Range(Cells(2, j), Cells(Lr2, j + 1)).Value = Range(Cells(2, j), Cells(Lr2, j + 1)).Value
Exit For
End If
Next j

End If

End Sub

 

[KalilMe]

well done ! this is exactly what I want . just last thing can you mod the code and make any empty cell should be hyphen "-" and without effect the subtracting . it should n't show error when subtract in COL BALANCE

 

[maabadi]

Try this:

Sub Macro2()
Dim Lr1 As Long, Lr2 As Long, Lc As Long, j As Long
Lr1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Lc = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
If Range("E2").Value = "" Then
Range("E2:F" & Lr2).Formula = "=INDEX(Sheet1!E$2:E$" & Lr2 & " ,MATCH(1,INDEX((Sheet1!$B$2:$B$" & Lr2 _
& " =$B2)*(Sheet1!$C$2:$C$" & Lr2 & " =$C2)*(Sheet1!$D$2:$D$" & Lr2 & " =$D2),0,1),0),1)"
Range("G2:G" & Lr2).Formula = "=IFERROR(E2-F2,""-"")"
Range("E2:F" & Lr2).Value = Range("E2:F" & Lr2).Value

Else
For j = 8 To Lc Step 3
If Cells(2, j).Value = "" Then
Range(Cells(2, j), Cells(Lr2, j + 1)).Formula = "=INDEX(Sheet1!E$2:E$" & Lr2 & " ,MATCH(1,INDEX((Sheet1!$B$2:$B$" & Lr2 _
& " =$B2)*(Sheet1!$C$2:$C$" & Lr2 & " =$C2)*(Sheet1!$D$2:$D$" & Lr2 & " =$D2),0,1),0),1)"
Range("G2:G" & Lr2).Copy Range(Cells(2, j + 2), Cells(Lr2, j + 2))
Range(Cells(2, j), Cells(Lr2, j + 1)).Value = Range(Cells(2, j), Cells(Lr2, j + 1)).Value
Exit For
End If
Next j
End If
End Sub

 

[KalilMe]

thanks
but it shows a problem . I mean this - should replace of the empty cell . the empty cell considers 0 . so when in COL PURCHASE is 5 and COL SALES "-" then the COL BALANCE should be 5 but your code shows" -" in COL BALANCE . may you fix it , please ?

 

[maabadi]

Why you don't Replace "-" at the Purchase or Sale Col with 0?
Replace this line

Range("G2:G" & Lr2).Formula = "=IFERROR(E2-F2,""-"")"

With

Range("G2:G" & Lr2).Formula = "=IFERROR(E2-F2,IF(ISNUMBER(E2),E2,F2*-1))"

 

[KalilMe]

fantastic ! thanks so much for every thing

 

[maabadi]

You're Welcome & Thanks for Follow-up.