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]

Try This With CTRL+SHIFT+ENTER

Book1
	A	B	C	D	E	F	G	H
1	ITEM	COMMIDETY	TYPE	ORIGIN	PURCHASE	SALES	BALANCE	PURCHASE
2	1	FR	BANANA	SO	55	5	50
3	2	FR	BANANA	SO1	10	0	10
4	3	FO1	TUNE160G	SP	22	5	17
5	4	FO1	TUNE160G	PO	10	10	0
6	5	VEG	TOMATO	EG	5	0	5
7	6	FO1	TUNE160G	SPL	5	-	#VALUE!
8
Sheet2

Cell Formulas
Range	Formula
E2:F7	E2	=IFERROR(INDEX(Sheet1!E$2:E$7,MATCH(1,($B2=Sheet1!$B$2:$B$7)*($C2=Sheet1!$C$2:$C$7)*($D2=Sheet1!$D$2:$D$7),0)),0)
G2:G7	G2	=E2-F2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KalilMe]

thanks
honestly I prefer to apply by vba . if you can do it by vba will be a great because my real data about 3000 rows and increasable
about your formula I try putting in E2 but gives me zero .

 

[maabadi]

about your formula I try putting in E2 but gives me zero

After Input Formula You Should Press

Try This With CTRL+SHIFT+ENTER

I Work On it For VBA.

 

[maabadi]

Try this:

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

 

[KalilMe]

thanks
but I said

every time run the macro should search for empty columns(PURCHASE , SALES) and populate the values based on what is existed in sheet1.

I no know if it's not clear .when I repeate running macro should populate the values to next columns contain PURCHASE , SALES . so after run first time macro should poulate values in COL H,I and if I run again should populate the values in COLS K,L and so on every time run macro should populate the values into next columns PURCHASE & SALES

I hope this explenation helps you

 

[maabadi]

Are Data should be Taken from same Columns at Sheet1. For e.g. Take H Data from sheet 2 from H column of Sheet1?

 

[KalilMe]

there is no COL H in sheet1 . it always supposes taking the values from COLS E,F from sheet1 after matching with sheet2 but the columns in sheet2 when populate values change continously when run macro repeatedly

 

[maabadi]

Then you say you want run macro to see same result at column E & H and ....
Why?

 

[KalilMe]

yes , it should show in COLS PURCHASE, SALES in sheet2 because the data in sheet2 imports from another file every month .then the values will change in sheet1 and should copy to next columns representatves next month