Convert horizontal data to vertical with running balance

[ExcelNewbie2020]

have this data arrange horizontally, i would like to convert it vertically together with the name with running balance (total). Also the date should be sorted.. would it be possible using formula?..thanks

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NAME	DATE	AMT	DATE	AMT	DATE	AMT					EXPECTED RESULT (FORMULA HERE)
2	A	9-Oct-2024	94	22-May-2024	47	19-Jun-2024	33					DATE	NAME	AMT	TOTAL
3	B	30-Sep-2024	75	29-Sep-2024	80	12-Apr-2024	71					12-Apr-24	B	71	71
4	C	31-Dec-2024	25	1-Dec-2024	63	19-May-2024	33					19-May-24	C	33	104
5	D	10-Jul-2024	60	25-Jun-2024	12	15-Dec-2024	54					22-May-24	A	47	151
6												19-Jun-24	A	33	184
7												25-Jun-24	D	12	196
8												10-Jul-24	D	60	256
9												29-Sep-24	B	80	336
10												30-Sep-24	B	75	411
11												09-Oct-24	A	94	505
12												01-Dec-24	C	63	568
13												15-Dec-24	D	54	622
14												31-Dec-24	C	25	647
15
16
Sheet1

 

[hagia_sofia]

Try this:

=LET(
i,A2:G5,
array,SORT(VSTACK(CHOOSECOLS(i,2,1,3),CHOOSECOLS(i,4,1,5),CHOOSECOLS(i,6,1,7)),1),
total,SCAN(0,CHOOSECOLS(array,3),LAMBDA(a,b,a+b)),
HSTACK(array,total))

 

[hagia_sofia]

If you want it with headers too:

=LET(
i,A2:G5,
array,SORT(VSTACK(CHOOSECOLS(i,2,1,3),CHOOSECOLS(i,4,1,5),CHOOSECOLS(i,6,1,7)),1),
scan,SCAN(0,CHOOSECOLS(array,3),LAMBDA(a,b,a+b)),
headers,HSTACK(B1,A1,C1,"TOTAL"),
VSTACK(headers,HSTACK(array,scan)))

 

[ExcelNewbie2020]

Try this:

=LET(
i,A2:G5,
array,SORT(VSTACK(CHOOSECOLS(i,2,1,3),CHOOSECOLS(i,4,1,5),CHOOSECOLS(i,6,1,7)),1),
total,SCAN(0,CHOOSECOLS(array,3),LAMBDA(a,b,a+b)),
HSTACK(array,total))

it worked..thanks

 

[alansidman]

Here is an alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"NAME"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Amount", each if Text.StartsWith([Attribute], "AMT") then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Up",{{"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Text.StartsWith([Attribute], "DATE")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Value", "Date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each List.Sum(List.FirstN(#"Renamed Columns"[Amount], [Index] ) )), 
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
    #"Removed Columns1"