Consolidate/SUM entries for a specific date and time

[treeleaf20]

All,
I have a table with something like the following data:

Type	Buy	Cur.	Sell	Cur.	Exchange	Trade Group	Comment	Trade Date
Trade	69.86​	CUR1	505.1879792​	USD	Exchange1			26.11.2022 10:33
Trade	67.1​	CUR1	485.3636898​	USD	Exchange1			26.11.2022 10:33
Trade	67.11​	CUR1	485.5032684​	USD2	Exchange1			26.11.2022 10:33
Trade	280.6​	CUR2	2029.983864​	USD	Exchange1			23.11.2022 14:21
Trade	100​	CUR2	723.444​	USD	Exchange1			23.11.2022 14:21
Trade	126.38​	CUR1	914.2885272​	USD	Exchange1			21.11.2022 08:53
Trade	21.02​	CUR1	152.0679288​	USD	Exchange1			21.11.2022 08:53
Trade	478.83​	CUR1	3464.546693​	USD	Exchange1			21.11.2022 08:53

I'd like to consolidate/sum the values in Column B and Column D based on the trade date and if the currency is the same in Column C and E. The above table would create the following entries in a new worksheet:

Type	Buy	Cur.	Sell	Cur.	Exchange	Trade Group	Comment	Trade Date
Trade	136.96​	CUR1	990.6​	USD	Exchange1		26.11.2022 10:33
Trade	67.11​	CUR1	485.5​	USD2	Exchange1		26.11.2022 10:33
Trade	380.6​	CUR2	2753.4​	USD	Exchange1		23.11.2022 14:21
Trade	626.23​	CUR1	4530.9​	USD	Exchange1		21.11.2022 08:53

Is there a macro or something that would accomplish this easily? Thanks in advance.

 

[arthurbr]

Perhaps try a Pivot Table?

 

[SanjayGMusafir]

There can be many ways to achieve that. It shall all depend on your version of Excel

Please update the same. You can us e the link below to do that

 

[treeleaf20]

Updated with version. Thank you.

 

[SanjayGMusafir]

As @arthurbr said Pivot Tables is one option.

With Excel 365 in you can try Filter Function and SUMIFS to bring total trade Values

• Unfortunately my Excel version doesn't support VStack function. Else I would have shown you the way to do above using one single formula.
• I remember @Fluff using the same in one of the solutions he gave.

I was also thinking about Pivotby Function. but that is only available in Beta versions so far.

One Suggestion -

• Better keep Trade Date and Trade time in two separate Columns, else soon it will become challenge for you to bring together transactions on a single day.

 

[treeleaf20]

I need to upload it back into an online tool. I would need to play around with a PivotTable. I'm not sure how easy it is to do that and get the same layout I had in the table.

 

[treeleaf20]

I can't seem to get the pivottable to work and get the output in the same layout that I had. Is there a way to do this via formulas?

 

[Alex Blakenburg]

Have you considered using Power Query ? If you don't have any VBA experience Power Query is an easier learning curve.
I knocked it up in about 5 mins.
It has raised some questions:
• Do you really want to group by Date AND time ? Having multiple trades with the exact time seems a bit unlikely.
• If you want Type, Exchange, Trade Group, Comment in the output then we should group by those as well. Are they going to fall in line with the Currency grouping ? I would have thought we need to drop at least the comment column and your results column misalignment seems to point in that direction.

PQ Output

20240708 VBA or PQ Summarise Trades treeleaf20.xlsm
	A	B	C	D	E	F	G	H	I
1	Type	Buy Sum	Cur.	Sell Sum	Cur.2	Exchange	Trade Group	Comment	Trade Date
2	Trade	136.96	CUR1	990.551669	USD	Exchange1			26/11/2022 10:33
3	Trade	67.11	CUR1	485.5032684	USD2	Exchange1			26/11/2022 10:33
4	Trade	380.6	CUR2	2753.427864	USD	Exchange1			23/11/2022 14:21
5	Trade	626.23	CUR1	4530.903149	USD	Exchange1			21/11/2022 8:53
Table1

PQ Query

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Buy", type number}, {"Cur.", type text}, {"Sell", type number}, {"Cur.2", type text}, {"Exchange", type text}, {"Trade Group", type any}, {"Comment", type any}, {"Trade Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type", "Cur.", "Cur.2", "Exchange", "Trade Group", "Comment", "Trade Date"}, {{"Buy Sum", each List.Sum([Buy]), type nullable number}, {"Sell Sum", each List.Sum([Sell]), type nullable number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Type", "Buy Sum", "Cur.", "Sell Sum", "Cur.2", "Exchange", "Trade Group", "Comment", "Trade Date"})
in
    #"Reordered Columns"

 

[treeleaf20]

Thank you, this worked fantastic! Didn't know that was an option.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.