Combining transactions

[kevmono]

Good evening,

There are occasions when I am presented with line level detail and need to sum related lines into a single transaction. At the moment I sort and subtotal related lines and copy detail from the cells above into subtotal lines.
This be as follows:

Date Unique Reference Line Number Value

xx XXX 1 10
xx XXX 2 25

xx XXX total 35

On the above, I would like to show one row showing detail held in total line only. If there is one row return one row only etc.

Can anybody help with how to show all the detail in a single row.

Thanks for reading.

Kevin

 

[sandy666]

try PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]xx XXX[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

10​

[/td][td][/td][td=bgcolor:#E2EFDA]xx XXX[/td][td=bgcolor:#E2EFDA]

35​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]xx XXX[/td][td]

2​

[/td][td]

25​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Sum", each List.Sum([Column3]), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[kevmono]

Thank you, I will try when I return home. This looks a lot more simple than my convoluted solution.

 

[kevmono]

Perfect, thank you

 

[sandy666]

You are welcome