subtotal and No. of batches against material code

Manoj K

New Member
Joined
Oct 22, 2021
Messages
35
Office Version
  1. 2013
Platform
  1. Windows
I have an excel file containing material code, Batch No. quantity manufactured and some other columns with more than 75 rows. One material code may be available in different rows. I want material code wise “subtotal for quantity manufactured” and “total number batches manufactured” against each material code.
Result can be in same sheet or different sheet.


1740815696656.png




Please help.
 
Power Query Solution

Book1
ABCDEFG
2MaterialBatchQuantityMaterialCountSum
35007918XYZ2703478,0005007918178000
45008389XY72011152310000500838949498000
55004756XYZ0240550118050047561501180
65000620XYZ24044480720500062031446300
75000669XYZ0029571517050006691715170
85005938XYZ2187428173050059383831690
95001514XYZ008151266000500151422042000
105006965XYZ246241024920500696522054160
115000620XYZ000254861605006104150853
125001514XYZ00835776000
135008389XYZ011552382000
145008389XYZ011552394000
155008389XYZ011252412000
165006965XYZ246341029240
175005938XYZ21894274530
185005938XYZ21884275430
195000620XYZ00035479420
205006104XYZ0086550,853
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Material"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Sum", each List.Sum([Quantity]), type number}})
in
    #"Grouped Rows"
 
Upvote 0
Book1.xlsm
ABCDEFGH
1MaterialBatchQuantityMaterialBatchQuantity
25007918XYZ27034780005007918178000
35008389XY72011152310000500838949498000
45004756XYZ0240550118050047561501180
55000620XYZ24044480720500062031446300
65000669XYZ0029571517050006691715170
75005938XYZ2187428173050059383831690
85001514XYZ008151266000500151422042000
95006965XYZ24624102492050069652 2054160
105000620XYZ000254861605006104150853
115001514XYZ00835776000   
125008389XYZ011552382000   
135008389XYZ011552394000   
145008389XYZ011252412000   
155006965XYZ246341029240   
165005938XYZ21894274530   
175005938XYZ21884275430   
185000620XYZ00035479420   
195006104XYZ0086550853   
Sheet4
Cell Formulas
RangeFormula
F2:F19F2=IFERROR(INDEX($A$2:$A$19,AGGREGATE(15,6,ROW($A$2:$A$19)/ISERROR(MATCH($A$2:$A$19,$F$1:$F1,0)),1)-ROW($A$1)),"")
G2:G19G2=IF($F2="","",COUNTIF($A$2:$A$19,$F2))
H2:H19H2=IF($F2="","",SUMIF($A$2:$A$19,$F2,$C$2:$C$19))

Formulas in F2,G2 and H2 are copied down
 
Upvote 0
Another option is to use Excel's built-in Pivot Table feature (found on the 'Insert' ribbon tab)

25 03 01.xlsm
ABCDEFG
1MaterialBatchQuantityValues
25007918XYZ2703478000MaterialCount of BatchSum of Quantity
35008389XY72011152310000500062031446300
45004756XYZ0240550118050006691715170
55000620XYZ24044480720500151422042000
65000669XYZ0029571517050047561501180
75005938XYZ2187428173050059383831690
85001514XYZ0081512660005006104150853
95006965XYZ246241024920500696522054160
105000620XYZ000254861605007918178000
115001514XYZ00835776000500838949498000
125008389XYZ011552382000Grand Total1817217353
135008389XYZ011552394000
145008389XYZ011252412000
155006965XYZ246341029240
165005938XYZ21894274530
175005938XYZ21884275430
185000620XYZ00035479420
195006104XYZ0086550853
20
PT
 
Upvote 0
I did not see the Material numbers are in Ascending order. It is set right.
Book1.xlsm
ABCDEFGH
1MaterialBatchQuantityMaterialBatchQuantity
25007918XYZ2703478000500062031446300
35008389XY7201115231000050006691715170
45004756XYZ02405501180500151422042000
55000620XYZ2404448072050047561501180
65000669XYZ0029571517050059383831690
75005938XYZ218742817305006104150853
85001514XYZ008151266000500696522054160
95006965XYZ2462410249205007918178000
105000620XYZ00025486160500838949498000
115001514XYZ00835776000   
125008389XYZ011552382000   
135008389XYZ011552394000   
145008389XYZ011252412000   
155006965XYZ246341029240   
165005938XYZ21894274530   
175005938XYZ21884275430   
185000620XYZ00035479420   
195006104XYZ0086550853   
Sheet4
Cell Formulas
RangeFormula
F2:F19F2=IFERROR(AGGREGATE(15,6,($A$2:$A$19)/ISERROR(MATCH($A$2:$A$19,$F$1:$F1,0)),1),"")
G2:G19G2=IF($F2="","",COUNTIF($A$2:$A$19,$F2))
H2:H19H2=IF($F2="","",SUMIF($A$2:$A$19,$F2,$C$2:$C$19))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top