Count cells with same value and create summary

[skpma]

Hello everyone

I need to create some kind of a summary for an order.
The orderfile looks like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Orderday	Ordernumber	Storenumber	Makernumber	Articlenumber	Articlename	Packagecontent	Unit	Price	Amount	Arrivalday	Time
2	20200622	728001	1023	45996	K000141	Article A	150	Single	25	1	20200625	1
3	20200622	729001	1032	45198	K000148	Article B	60	Single	20	1	20200625	0
4	20200622	730001	1075	45996	K000141	Article A	150	Single	25	1	20200625	0
5	20200622	731001	1077	45197	K000147	Article C	36	Single	20	1	20200625	0
6	20200622	731002	1077	45198	K000148	Article B	60	Single	20	1	20200625	0
7	20200622	732001	1078	45996	K000141	Article A	150	Single	25	1	20200625	0
8	20200622	734002	1088	45996	K000141	Article A	150	Single	25	1	20200625	0
Sheet1

On a second sheet I want to count the number of occurrences of an article, copy some values and give out the total price amount. It should be based on the occurrence of the maker number.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Shippingday	Deliveryday	Arrivalday	Makernumber	Articlenumber	Articlename	Packagecontent	Unit	Occurrence	Price	Total Amount
2			20200625	45996	K000141	Article A	150	Single	4	25	100
3			20200625	45198	K000148	Article B	60	Single	2	20	40
4			20200625	45197	K000147	Article C	36	Single	1	20	20
Sheet2

Cell Formulas
Range	Formula
K2:K4	K2	=I2*J2

Is this achievable? I'm thankful for every hint, doesn't have to be the solution.
Thanks in advance.

 

[alansidman]

Using Power Query/Get and Transform.
1. Bring your table into PQ and Group on the MakeNumber

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orderday", Int64.Type}, {"Ordernumber", Int64.Type}, {"Storenumber", Int64.Type}, {"Makernumber", Int64.Type}, {"Articlenumber", type text}, {"Articlename", type text}, {"Packagecontent", Int64.Type}, {"Unit", type text}, {"Price", Int64.Type}, {"Amount", Int64.Type}, {"Arrivalday", Int64.Type}, {"Time", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Makernumber"}, {{"Occurance", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

2. Replicate your table that you just amended, but remove all steps except the first

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

3. Merge your two tables with a Left Inner Join
4. Expand your table to include all columns except MakeNumber which is already there
5. Move your Occurance field to the end of the table.
6. Multiply the Occurance by the Price in an added column

let
    Source = Table.NestedJoin(Table1, {"Makernumber"}, #"Table1 (2)", {"Makernumber"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Orderday", "Ordernumber", "Storenumber", "Articlenumber", "Articlename", "Packagecontent", "Unit", "Price", "Amount", "Arrivalday", "Time"}, {"Orderday", "Ordernumber", "Storenumber", "Articlenumber", "Articlename", "Packagecontent", "Unit", "Price", "Amount", "Arrivalday", "Time"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table1 (2)",{"Makernumber", "Orderday", "Ordernumber", "Storenumber", "Articlenumber", "Articlename", "Packagecontent", "Unit", "Price", "Amount", "Arrivalday", "Time", "Occurance"}),
    #"Removed Duplicates" = Table.Distinct(#"Reordered Columns", {"Makernumber"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Removed Duplicates", "Multiplication", each [Price] * [Occurance], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Total"}})
in
    #"Renamed Columns"

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Makernumber	Orderday	Ordernumber	Storenumber	Articlenumber	Articlename	Packagecontent	Unit	Price	Amount	Arrivalday	Time	Occurance	Total
2	45996	20200622	728001	1023	K000141	Article A	150	Single	25	1	20200625	1	4	100
3	45198	20200622	729001	1032	K000148	Article B	60	Single	20	1	20200625	0	2	40
4	45197	20200622	731001	1077	K000147	Article C	36	Single	20	1	20200625	0	1	20
Sheet4

 

[skpma]

Thank you for your answer.
This works, and is good for a one time use but I have to do this multiple times a day with different workbooks. Would require training on user side too.
Was more thinking about a VBA macro or formula where everything is automated.