Summarize data

HM

Hi,

I'm trying to summarize data but keep certain fields at the high level but the sales fields summed up. I've used SUM for the sales fields and CONCAT for the Name field but I'm not quite sure what to use for the Level, Supplier and Cost field.

Data:

Record #	Level	Supplier	Name	Cost	Sales - Month 1	Sales - Month 2	Sales - Month 3
1	Low	C2	D	10	15	9	1
2	Med	C1	B	40	11	28	61
3	Med	C2	C	20	10	2	6
4	High	C1	A	100	20	30	15
5	Low	C3	E	50	64	21	81
6	Low	C3	F	60	1	7	9

Summarized Output:

Record #	Level	Supplier	Name	Cost	Sales - Month 1	Sales - Month 2	Sales - Month 3
1	High	C1	A,B,C,D,E,F	100	121	97	173

Any assistance is appreciated - thanks

Joe_Lipski

Hi @HM you need to sort the data by name before summarising. Image below and module attached to get your desired output, although some of the logic doesn't make perfect sense to me (e.g. the names don't roll up into the Level and Record # that you want) but you will understand what you want bettter.

@s_pichaipillai I just downloaded your solution and just a little tip for you (if you didn't know) you can paste directly into the top row of a text input so the headers go in correctly (rather than using the dynamic rename) - Click in the '...'  and then click the paste icon above.

JL Solution HM.yxmd