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]

One of the issues I am having with your data is that it does not match consistently. You have spaces in one record and in the expected "matching" record therer are no spaces. Your data needs to be consistent for any type of matching to happen in Excel. Suggest you check your data and then try to reload so that we don't have to retype and figure out which ones don't match in order to give you a workable solution. Help us to help you by giving us reliable data.

 

[tubrak]

thanks . can the excel ignore any different spaces ? just matching based on the words into cells .

 

[alansidman]

Cannot. Does not have AI. Matches on a String. If not exact then it does not pair up.

 

[tubrak]

ok I will check it and re-upload data again soon.

 

[tubrak]

hope this help . cleaning some spaces

stock.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

stock.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

stock.xlsm
	A	B	C
1	S.N	ITEM	SELLING
2	1	TR 2.5M100**123H K/L	8
3	2	TR 2.5M100* 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

stock.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
STOCK 7-15-2022

 

[alansidman]

Since you have Excel 2019, you have power query available to you. It is on the Data Tab and is called Get and Transform Data.

Load each of your tables into PQ and then join them as shown in the following Mcode.

Load the Item Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QTY", Int64.Type}})
in
    #"Changed Type"

Load the buying table

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BUYING", Int64.Type}})
in
    #"Changed Type"

Load the selling table

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SELLING", Int64.Type}})
in
    #"Changed Type"

Join the item table with the buying table and add the quantities

let
    Source = Table.NestedJoin(Items, {"ITEM"}, Buying, {"ITEM"}, "Buying", JoinKind.LeftOuter),
    #"Expanded Buying" = Table.ExpandTableColumn(Source, "Buying", {"BUYING"}, {"BUYING.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Buying",null,0,Replacer.ReplaceValue,{"BUYING.1"}),
    #"Inserted Addition" = Table.AddColumn(#"Replaced Value", "Addition", each [QTY] + [BUYING.1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Addition",{"QTY", "BUYING.1"})
in
    #"Removed Columns"

Join the selling table to the joined table previously and subtract quantities

let
    Source = Table.NestedJoin(Merge1, {"ITEM"}, Selling, {"ITEM"}, "Selling", JoinKind.LeftOuter),
    #"Expanded Selling" = Table.ExpandTableColumn(Source, "Selling", {"SELLING"}, {"SELLING.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Selling",null,0,Replacer.ReplaceValue,{"SELLING.1"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [Addition] - [SELLING.1], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "QTY"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Addition", "SELLING.1"})
in
    #"Removed Columns"

End result will look as follows.

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

 

[alansidman]

Correction to above as time to edit has expired. Noticed an error in my first presentation.
the first join should be as follows:

let
    Source = Table.NestedJoin(Items, {"ITEM"}, Buying, {"ITEM"}, "Buying", JoinKind.FullOuter),
    #"Expanded Buying" = Table.ExpandTableColumn(Source, "Buying", {"ITEM", "BUYING"}, {"ITEM.1", "BUYING.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Buying", "Custom", each if[ITEM]=null then [ITEM.1] else [ITEM]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ITEM", "ITEM.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"S.N", "Custom", "QTY", "BUYING.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"BUYING.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"QTY"}),
    #"Inserted Addition" = Table.AddColumn(#"Replaced Value1", "Addition", each [QTY] + [BUYING.1], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Addition",{"QTY", "BUYING.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "ITEM"}})
in
    #"Renamed Columns"

and the end result should look like the following

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

 

[tubrak]

thanks but it gives error expression sentaxerror

 

[alansidman]

I did not save the workbook, so I will have to try and recreate again and then I will post the workbook to a third party site so you can review completely. Right now it is 2 am and I need some sleep. Hiking in the morning. Will look at it later in the day.

Alan