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]

I think you need to use the 'group by' option, give that a try and let me know how you get on.

If this doesn't work please let me know the output you would like as I'm assuming the output you have posted is the one you are currently getting rather than the one you want.

[s_pichaipillai]

HM,

i assume you need to concatenate  and summarize based on your cost which is High

Please check the attached one, it may give an idea how to proceed and fine tune your requirement

Thanks

Sar

[HM]

Hi,

Apologies for the confusion - the summarised output is my desired result.

The actual result (because I'm grouping by supplier which is mostly unique) is:

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

[s_pichaipillai]

Hi HM,

not a problem, now its very simple , just modify summarize tool as below

PFA modified one

i went offfline and sorry for the delay .

thanks

Sar

[HM]

That's completely fine - I appreciate your help.

Sorry - I was replying to the question above. The result I want is the one line but there isn't any order to the name which makes it difficult:

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

But the result I'm getting which i don't want is:

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

[Joe_Lipski · Accepted solution]

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.

[s_pichaipillai]

Joe,

thanks for the tip.. i was not aware of it :)

[HM]

Hi Joe,

Thank you for your help - it's getting closer to my desired result! The only issue I have is that the name cannot be sorted to represent the level. My real data looks more like the below:

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	A	20	10	2	6
4	High	C1	C	100	20	30	15
5	Low	C3	E	50	64	21	81
6	Low	C3	F	60	1	7	9

Apologies for changing the data after you've presented a potential solution