VBA or PQ or Formula or Pivot Table?

[nburaq]

Hi Gents,
I would like to compare two sheets having 3 common dependent in each sheet and would like to see the difference at another sheet or in sheet2 specified column as "Difference" I tried Pivot table and xlookup but didn't work for me and I think this one needs VBA or PQ. I even don't know where to post this question... I attach the screenshots and gonna try to upload the data as well (xlbb crashed) Thanks for help and comments!

 

[mart37]

Have you tried SUMIFS or SUMPRODUCT?

 

[nburaq]

Have you tried SUMIFS or SUMPRODUCT?

No, how is the way to do it? I tried to do it using two pivot tables but no luck.If you can share the solution I will be appreciated

 

[mart37]

To count the total of Qty in sheet1:

=SUMIFS(sheet1!C:C,sheet1!A:A,sheet2!B2,sheet1!B:B,sheet2!C2)

 

[nburaq]

To count the total of Qty in sheet1:

=SUMIFS(sheet1!C:C,sheet1!A:A,sheet2!B2,sheet1!B:B,sheet2!C2)

Hi,
Thanks for the solution! it seems SUMSIF calculates the total number of materials used for each item in sheet1 thats why I extracted this formula from column D in sheet 2 like:

=D2-SUMIFS(sheet1!C:C,sheet1!A:A,sheet2!B2,sheet1!B:B,sheet2!C2)

 

[alansidman]

A power query solution as an alternative
1. Bring each table into the PQ editor
2. Join the tables with a left inner join

let
    Source = Table.NestedJoin(Table1, {"Item", "Material"}, Table2, {"Item", "Material"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Qty"}, {"Table2.Qty"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,0,Replacer.ReplaceValue,{"Table2.Qty"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [Qty] - [Table2.Qty], Int64.Type)
in
    #"Inserted Subtraction"

Book5
	A	B	C	D	E
1	Item	Material	Qty	Table2.Qty	Subtraction
2	201	15525	1	4	-3
3	210	15528	3	1	2
4	240	12612	4	0	4
5	322	NA	0	0	0
6	542	12612	3	0	3
7	542	12613	1	0	1
8	542	12618	2	0	2
Merge1

 

[nburaq]

A power query solution as an alternative
1. Bring each table into the PQ editor
2. Join the tables with a left inner join

let
    Source = Table.NestedJoin(Table1, {"Item", "Material"}, Table2, {"Item", "Material"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Qty"}, {"Table2.Qty"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,0,Replacer.ReplaceValue,{"Table2.Qty"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [Qty] - [Table2.Qty], Int64.Type)
in
    #"Inserted Subtraction"

Book5
	A	B	C	D	E
1	Item	Material	Qty	Table2.Qty	Subtraction
2	201	15525	1	4	-3
3	210	15528	3	1	2
4	240	12612	4	0	4
5	322	NA	0	0	0
6	542	12612	3	0	3
7	542	12613	1	0	1
8	542	12618	2	0	2
Merge1

Thanks for the solution! yesterday I tried to do this with PQ with inner join but it was giving me null values since PQ could not find to match. Now I see where I made mistake in PQ! Thanks again for the code! Such a great community!

 

[nburaq]

Regarding to my previous problem, I have one more question and if you could help me to solve it I will be so glad;
If I add one more column named "New Material" in sheet2, and this column will check whether the material used with the same item number in sheet2 is already available in sheet1 with the same item number then it will result like this is a new material ordered so "YES" or it is already in sheet1 with registered the same item number so "NO" I solved this partly using XLOOKUP like;

=XLOOKUP($B2&$C2;Sheet1!$A$2:$A$8&Sheet1!&B2:B8;Sheet1!$C$2:$C$8;"YES";0)

And Sheet1:

Book1
	A	B	C
1	Item	Material	Qty
2	201	15525	1
3	210	15528	3
4	240	12612	4
5	322	NA	0
6	542	12612	3
7	542	12613	1
8	542	12618	2
Sheet1

Sheet2:

Book1
	A	B	C	D	E	F
1	Order	item	Material	Qty	Difference	New Material (Y/N)
2	10	201	15525	4	3	N
3	20	210	15528	1	2	N
4	25	542	19123	1	1	N
5	35	201	16001	1	1	Y
Sheet2

Thanks again all your help!

 

[nburaq]

Ok I am almost close to the solution by myself but having one issue using IFERROR IF AND MATCH functions. I would like match function not to do anything if it sees a value as "NA" in material column in sheet2. so my formula so far:

=IFERROR(IF(MATCH($B2&$C2;Sheet1!$A$2:$A$1603&Sheet1!$B$2:$B$1603;0);"NO");"YES")

 

[nburaq]

Ok I am almost close to the solution by myself but having one issue using IFERROR IF AND MATCH functions. I would like match function not to do anything if it sees a value as "NA" in material column in sheet2. so my formula so far:

=IFERROR(IF(MATCH($B2&$C2;Sheet1!$A$2:$A$1603&Sheet1!$B$2:$B$1603;0);"NO");"YES")

ok I solved the problem by adding extra IF at the top of the function so

=IF(C2="NA";"NO";IFERROR(IF(MATCH($B2&$C2;Sheet1!$A$2:$A$1603&Sheet1!$B$2:$B$1603;0);"NO");"YES"))