merge rows based on cell value using aggregates

[samh92]

Hi,

I'm relatively new to VBA and I've been searching the forum for an answer but I'm struggling to implement any of the solutions I've come across.

I have a file that's produced daily and shows data for the previous days performance. I need to merge all of the data in the file to produce a daily total.

I've provided a snip of the data below:

What I'm trying to achieve is:

• Merge all rows based on 'Product_Type'.
• Columns in blue need to be summed
• Columns in Green need to be an average
• Time column is irrelevant so can be removed

Given the above example the final result should look like this:

Ideally id like to be able to run it as a macro as this task will need to be done daily and the files have 900+ rows.

Other examples that I've come across sort the data by a column and then merge cells but this doesn't total the other columns or produce an average which is what I need.

any help would be appreciated.

thank you.

 

[alansidman]

Cannot manipulate data in a picture. Please reload your samples using XL2BB.

 

[samh92]

2022-07-22 - SanOps_Q_Geoban(490).csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	Time	Product	Channel	Product_Type	ResourcePlanning	Offered_Net_Abn5Sec	Offered_Inc_Abn5Sec	Handled	Abandoned_Net_Abn5Sec	Abandoned_Inc_Abn5Sec	Abn5Sec	SL_Number	SL_Number_CR	Time_to_Answer	Time_to_Abandon
2	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	ANMF		11	11	11	0	0	0	1	9.09	2521	0
3	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	ANMF AMS		2	2	1	1	1	0	0	0	399	121
4	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	ANMF Flexi		2	2	2	0	0	0	0	0	645	0
5	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	ANMF Inter		1	1	1	0	0	0	0	0	69	0
6	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	ANMF RedDed		6	6	5	1	1	0	0	0	1102	8
7	22/07/2022 00:00	1899-12-30 08:45:00	AccountsANMF	Geoban	w		3	3	3	0	0	0	0	0	671	0
8	22/07/2022 00:00	1899-12-30 08:45:00	Bereavement	Geoban	BnkSav		7	7	7	0	0	0	5	71.43	255	0
9	22/07/2022 00:00	1899-12-30 08:45:00	Bereavement	Geoban	BRVMNT Digital		2	2	2	0	0	0	2	100	16	0
10	22/07/2022 00:00	1899-12-30 08:45:00	Bereavement	Geoban	POA		3	3	3	0	0	0	2	66.67	115	0
11	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	ANMF		43	43	27	16	16	0	4	9.3	25778	10409
12	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	ANMF AMS		1	1	0	1	1	0	0	0	0	531
13	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	ANMF Flexi		1	1	0	1	1	0	0	0	0	1357
14	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	ANMF Inter		15	15	15	0	0	0	8	53.33	367	0
15	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	ANMF RedDed		15	15	7	8	8	0	0	0	7844	3423
16	22/07/2022 00:00	1899-12-30 09:30:00	AccountsANMF	Geoban	w		3	3	3	0	0	0	0	0	3391	0
2022-07-22 - SanOps_Q_Geoban(49

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time", "ResourcePlanning"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Product", "Channel", "Product_Type"}, {{"Total Offered Net", each List.Sum([Offered_Net_Abn5Sec]), type number}, {"Total Offered Inc", each List.Sum([Offered_Inc_Abn5Sec]), type number}, {"Total Handled", each List.Sum([Handled]), type number}, {"Total Abandoned Net", each List.Sum([Abandoned_Net_Abn5Sec]), type number}, {"Total Abandoned Inc", each List.Sum([Abandoned_Inc_Abn5Sec]), type number}, {"Total Abn", each List.Sum([Abn5Sec]), type number}, {"Total SL", each List.Sum([SL_Number]), type number}, {"Avg SL Number CR", each List.Average([SL_Number_CR]), type number}, {"Avg Time to Ans", each List.Average([Time_to_Answer]), type number}, {"Avg Time to Abandon", each List.Average([Time_to_Abandon]), type number}})
in
    #"Grouped Rows"

Date	Product	Channel	Product_Type	Total Offered Net	Total Offered Inc	Total Handled	Total Abandoned Net	Total Abandoned Inc	Total Abn	Total SL	Avg SL Number CR	Avg Time to Ans	Avg Time to Abandon
7/22/2022	AccountsANMF	Geoban	ANMF	54	54	38	16	16	0	5	9.195	14149.5	5204.5
7/22/2022	AccountsANMF	Geoban	ANMF AMS	3	3	1	2	2	0	0	0	199.5	326
7/22/2022	AccountsANMF	Geoban	ANMF Flexi	3	3	2	1	1	0	0	0	322.5	678.5
7/22/2022	AccountsANMF	Geoban	ANMF Inter	16	16	16	0	0	0	8	26.665	218	0
7/22/2022	AccountsANMF	Geoban	ANMF RedDed	21	21	12	9	9	0	0	0	4473	1715.5
7/22/2022	AccountsANMF	Geoban	w	6	6	6	0	0	0	0	0	2031	0
7/22/2022	Bereavement	Geoban	BnkSav	7	7	7	0	0	0	5	71.43	255	0
7/22/2022	Bereavement	Geoban	BRVMNT Digital	2	2	2	0	0	0	2	100	16	0
7/22/2022	Bereavement	Geoban	POA	3	3	3	0	0	0	2	66.67	115	0

 

[samh92]

That's perfect - thank you !

one question as I've never used power query before, why is there a call to 'removed columns' in the 'grouped rows' section? is it to compile the table without the columns ? I would have assumed that Table.removeColumns would handle that?

 

[alansidman]

That is just a reference to the prior step. In power query, you can reference prior steps which are out of sequence. This keeps the steps in order. In this case all the steps are in sequence. Click on the link in my signature to learn more about PQ

 

[samh92]

Thank you for the explanation. I really appreciate your help ! I'll have a look at the links now.

 

[samh92]

I've read the links in your signature and made some changes. The List.Average functions have been changed to List.Sum which is then divided by the sum of handled to give an average that better suits my needs.

I also want to merge all rows that have the value 'AccountsANMF' in the 'Product' column however I can only get it to merge them all using "Product_Type", each [Product_Type]{0}.

Should I be using the <> operator and specifying which Products not to merge?

= let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time", "ResourcePlanning"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Product", "Channel"}, {{"Product_Type", each [Product_Type]{0},"Total Offered Net", each List.Sum([Offered_Net_Abn5Sec]), type number}, {"Total Offered Inc", each List.Sum([Offered_Inc_Abn5Sec]), type number}, {"Total Handled", each List.Sum([Handled]), type number}, {"Total Abandoned Net", each List.Sum([Abandoned_Net_Abn5Sec]), type number}, {"Total Abandoned Inc", each List.Sum([Abandoned_Inc_Abn5Sec]), type number}, {"Total Abn", each List.Sum([Abn5Sec]), type number}, {"Total SL", each List.Sum([SL_Number]), type number}, {"Avg SL Number CR", each List.Sum([SL_Number_CR]), type number}, {"Avg Time to Ans", each List.Sum([Time_to_Answer])/List.Sum([Handled]), type number}, {"Avg Time to Abandon", each List.Sum([Time_to_Abandon])/List.Sum([Handled]), type number}})
in
    #"Grouped Rows"