calculation at the same column based on matching two sheets to show balance

[tubrak]

hi
I want to show the final values in column C in sheet ITEM after matching with column B for two sheets 1,2 . the calculation should sum the values in column C with sheet1 and subtracting from sheet2 and if there are new items in sheet1,2 but not are existed in sheet ITEM then should add to sheet ITEM for instance TR 200M45 MM 123H K/L SS=20+20-20 . with also brings the others items have already existed .so every time run the macro should creat new sheet based on date today with word STOCK and if change or update again in the same date(today) should update for sheet has already created and should create new sheet for next day . I put the result in sheet based on date(today) . my data are huge about more than 10000 rows
also posted here
calculation at the same column based on matching two sheets to show balance

stock1.xlsm
	A	B	C
1	S.N	ITEM	QTY
2	1	CTR 12-200M45 MM 123H K/L	30
3	2	TR 200M45MM LK H K/L	10
4	3	TTR 12200S45 MN 123	20
5	4	TR 1425/148V MN 123H K/L	15
6	5	TR 200M45 MM 123H K/L SS	20
7	6	TR 2.5M100* 123H K/L	10
8	7	TR 2.5M100**123H K/L	20
9	8	TR 1200M45MM LK H K/L	0
10	9	BTR-100***8 RRT-DE34230	20
11	10	BTR-100***8 RRT-DE34231	20
12	11	BTR-100***8 RRT-DE34232	20
13	12	BTR-100***8 RRT-DE34233	234
14	13	BTR-100***8 RRT-DE34234	44
15	14	BTR-100***8 RRT-DE34235	55
16	15	BTR-100***8 RRT-DE34236	212
17	16	BTR-100***8 RRT-DE34237	20
18	17	BTR-100***8 RRT-DE34238	20
ITEMS

stock1.xlsm
	A	B	C
1	S.N	ITEM	BUYING
2	1	TR 200M451NMM LK H K/L	10
3	2	TR 2.5M100**123H K/LM	20
4	3	CCTR 12-200M45 MM 123H K/L	15
5	4	TR 200M45 MM 123H K/L SS	20
6	5	TR 2.5M100*123H K/L	10
7	6	TTRM 12200S45 MN 1234	20
SHEET1

stock1.xlsm
	A	B	C
1	S.N	ITEM	SELLING
2	1	TR 2.5M1100**123H K/L	8
3	2	TR 2.5M1100*123H K/L	25
4	3	TR 1425/148V MN 123H K/L	15
5	4	TR 200M45 MM 123H K/L SS	20
6	5	TRMN 22.5M100* 123H K/L	10
7	9	TTRR 1200S45 MN 123	20
SHEET2

result

stock1.xlsm
	A	B	C
1	S.N	ITEM	QTY
2	1	CTR 12-200M45 MM 123H K/L	30
3	2	CCTR 12-200M45 MM 123H K/L	15
4	3	TR 200M45MM LK H K/L	10
5	4	TR 200M451NMM LK H K/L	10
6	5	TTR 12200S45 MN 123	20
7	6	TR 1425/148V MN 123H K/L	0
8	7	TR 200M45 MM 123H K/L SS	20
9	8	TR 2.5M100* 123H K/L	-5
10	9	TR 2.5M100**123H K/L	32
11	10	TRMN 22.5M100* 123H K/L	-10
12	11	TTRM 12200S45 MN 1234	-20
13	12	TTRR 1200S45 MN 123	-20
14	13	TR 1200M45MM LK H K/L	0
15	14	BTR-100***8 RRT-DE34230	20
16	15	BTR-100***8 RRT-DE34231	20
17	16	BTR-100***8 RRT-DE34232	20
18	17	BTR-100***8 RRT-DE34233	234
19	18	BTR-100***8 RRT-DE34234	44
20	19	BTR-100***8 RRT-DE34235	55
21	20	BTR-100***8 RRT-DE34236	212
22	21	BTR-100***8 RRT-DE34237	20
23	22	BTR-100***8 RRT-DE34238	20
24	23	BTR-100***8 RRT-DE34239	20
STOCK 7-15-2022

thanks

 

[alansidman]

Didn't wait -- here is the link to the file I recreated. You can look at each query and follow them step by step. You can see the expected results. I changed each of the query names from Query 1, Query 2, etc to more meaningful names.

Box

app.box.com

 

[tubrak]

Thanks
Two questions
First why gives me error despite of I make sure from the same structure data.?
Second does PQ deal with huge data as in vba?

 

[alansidman]

Don't know why you are getting an error. Would have to see your actual data to determine why. My guess is that either your data is not in the same layout as your sample or your tables are named differently. Also, note that my query names have been changed manually from query1 to items, etc. You will need to amend that to suit your specific case.

As to your second question. PQ can handle very big data sets. One advantage is that it can handle more than 1.6 million rows which is the max in Excel.

 

[tubrak]

As to your second question. PQ can handle very big data sets. One advantage is that it can handle more than 1.6 million rows which is the max in Excel.

impressive ! that's mean no need vba . rather can be slow when run macro for big data and crash the file .

 

[alansidman]

If you are truly interested in taking PQ to the next step, then I urge you to pick up Ken Puls and Miguel Escobars book
Master Your Data with Power Query in Excel and Power BI available on MrE's bookstore or Amazon