subtotal and No. of batches against material code

[Manoj K]

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.

Please help.

 

[alansidman]

Power Query Solution

Book1
	A	B	C	D	E	F	G
2	Material	Batch	Quantity		Material	Count	Sum
3	5007918	XYZ27034	78,000		5007918	1	78000
4	5008389	XY7201115	2310000		5008389	4	9498000
5	5004756	XYZ02405	501180		5004756	1	501180
6	5000620	XYZ24044	480720		5000620	3	1446300
7	5000669	XYZ00295	715170		5000669	1	715170
8	5005938	XYZ21874	281730		5005938	3	831690
9	5001514	XYZ00815	1266000		5001514	2	2042000
10	5006965	XYZ24624	1024920		5006965	2	2054160
11	5000620	XYZ00025	486160		5006104	1	50853
12	5001514	XYZ00835	776000
13	5008389	XYZ01155	2382000
14	5008389	XYZ01155	2394000
15	5008389	XYZ01125	2412000
16	5006965	XYZ24634	1029240
17	5005938	XYZ21894	274530
18	5005938	XYZ21884	275430
19	5000620	XYZ00035	479420
20	5006104	XYZ00865	50,853
Sheet1

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"

 

[kvsrinivasamurthy]

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Material	Batch	Quantity			Material	Batch	Quantity
2	5007918	XYZ27034	78000			5007918	1	78000
3	5008389	XY7201115	2310000			5008389	4	9498000
4	5004756	XYZ02405	501180			5004756	1	501180
5	5000620	XYZ24044	480720			5000620	3	1446300
6	5000669	XYZ00295	715170			5000669	1	715170
7	5005938	XYZ21874	281730			5005938	3	831690
8	5001514	XYZ00815	1266000			5001514	2	2042000
9	5006965	XYZ24624	1024920			5006965	2	2054160
10	5000620	XYZ00025	486160			5006104	1	50853
11	5001514	XYZ00835	776000
12	5008389	XYZ01155	2382000
13	5008389	XYZ01155	2394000
14	5008389	XYZ01125	2412000
15	5006965	XYZ24634	1029240
16	5005938	XYZ21894	274530
17	5005938	XYZ21884	275430
18	5000620	XYZ00035	479420
19	5006104	XYZ00865	50853
Sheet4

Cell Formulas
Range	Formula
F2:F19	F2	=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:G19	G2	=IF($F2="","",COUNTIF($A$2:$A$19,$F2))
H2:H19	H2	=IF($F2="","",SUMIF($A$2:$A$19,$F2,$C$2:$C$19))

Formulas in F2,G2 and H2 are copied down

 

[Peter_SSs]

Another option is to use Excel's built-in Pivot Table feature (found on the 'Insert' ribbon tab)

25 03 01.xlsm
	A	B	C	D	E	F	G
1	Material	Batch	Quantity			Values
2	5007918	XYZ27034	78000		Material	Count of Batch	Sum of Quantity
3	5008389	XY7201115	2310000		5000620	3	1446300
4	5004756	XYZ02405	501180		5000669	1	715170
5	5000620	XYZ24044	480720		5001514	2	2042000
6	5000669	XYZ00295	715170		5004756	1	501180
7	5005938	XYZ21874	281730		5005938	3	831690
8	5001514	XYZ00815	1266000		5006104	1	50853
9	5006965	XYZ24624	1024920		5006965	2	2054160
10	5000620	XYZ00025	486160		5007918	1	78000
11	5001514	XYZ00835	776000		5008389	4	9498000
12	5008389	XYZ01155	2382000		Grand Total	18	17217353
13	5008389	XYZ01155	2394000
14	5008389	XYZ01125	2412000
15	5006965	XYZ24634	1029240
16	5005938	XYZ21894	274530
17	5005938	XYZ21884	275430
18	5000620	XYZ00035	479420
19	5006104	XYZ00865	50853
20
PT

 

[kvsrinivasamurthy]

I did not see the Material numbers are in Ascending order. It is set right.

Book1.xlsm
	A	B	C	D	E	F	G	H
1	Material	Batch	Quantity			Material	Batch	Quantity
2	5007918	XYZ27034	78000			5000620	3	1446300
3	5008389	XY7201115	2310000			5000669	1	715170
4	5004756	XYZ02405	501180			5001514	2	2042000
5	5000620	XYZ24044	480720			5004756	1	501180
6	5000669	XYZ00295	715170			5005938	3	831690
7	5005938	XYZ21874	281730			5006104	1	50853
8	5001514	XYZ00815	1266000			5006965	2	2054160
9	5006965	XYZ24624	1024920			5007918	1	78000
10	5000620	XYZ00025	486160			5008389	4	9498000
11	5001514	XYZ00835	776000
12	5008389	XYZ01155	2382000
13	5008389	XYZ01155	2394000
14	5008389	XYZ01125	2412000
15	5006965	XYZ24634	1029240
16	5005938	XYZ21894	274530
17	5005938	XYZ21884	275430
18	5000620	XYZ00035	479420
19	5006104	XYZ00865	50853
Sheet4

Cell Formulas
Range	Formula
F2:F19	F2	=IFERROR(AGGREGATE(15,6,($A$2:$A$19)/ISERROR(MATCH($A$2:$A$19,$F$1:$F1,0)),1),"")
G2:G19	G2	=IF($F2="","",COUNTIF($A$2:$A$19,$F2))
H2:H19	H2	=IF($F2="","",SUMIF($A$2:$A$19,$F2,$C$2:$C$19))

 

[Manoj K]

could do using Pivot Table. Thanks

 

[Peter_SSs]

could do using Pivot Table. Thanks

You're welcome. Glad that worked for you. Thanks for the follow-up.