Get the individual entries from the running total which is across the row and continues to the next rows.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 500
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Entries from Running Total with Power Query
Power Query solution 1 for Extract Entries from Running Total, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Amt = List.Combine(List.Zip(List.Skip(Table.ToColumns(Source)))),
Amt2 = List.Split(
{Amt{0}} & List.Transform({1 .. List.Count(Amt) - 1}, each Amt{_} - Amt{_ - 1}),
3
),
Lista = List.Transform({0 .. List.Count(Amt2) - 1}, each {Source[Date]{_}} & Amt2{_}),
Sol = Table.FromRows(Lista, Table.ColumnNames(Source))
in
SolPower Query solution 2 for Extract Entries from Running Total, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res =
let
a = Table.AddColumn(Fonte, "tab", each List.RemoveFirstN(Record.FieldValues(_), 1))[tab],
b = List.Combine(a),
c = List.Zip({b, List.RemoveFirstN(b, 1)}),
d = List.RemoveNulls({b{0}} & List.Transform(c, each Number.Abs(_{0} - _{1}))),
e = List.Split(d, 3),
f = Fonte[Date]
in
Table.FromRows(
List.Transform(
{0 .. List.Count(f) - 1},
each List.RemoveNulls(List.Combine(List.Zip({{f{_}}, e{_}})))
),
Table.ColumnNames(Fonte)
)
in
resPower Query solution 3 for Extract Entries from Running Total, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Transform(S[Date], each {_}),
b = List.Combine(List.Transform(Table.ToRows(S), each List.Skip(_))),
c = List.InsertRange(b, 0, {0}),
d = List.Transform(List.Positions(c), each try c{_ + 1} - c{_} otherwise null),
e = List.Split(List.RemoveNulls(d), 3),
f = Table.FromRows(List.Transform(List.Positions(e), each a{_} & e{_})),
g = List.Zip({Table.ColumnNames(f), Table.ColumnNames(S)}),
Sol = Table.RenameColumns(f, g)
in
SolPower Query solution 4 for Extract Entries from Running Total, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
IndexCol = Table.AddIndexColumn(Unpivoted, "Index", 0, 1, Int64.Type),
CumulativeReverse = Table.AddColumn(
IndexCol,
"NewCol",
each if [Index] = 0 then [Value] else IndexCol[Value]{[Index]} - IndexCol[Value]{[Index] - 1}
),
Removed = Table.RemoveColumns(CumulativeReverse, {"Value", "Index"}),
Pivoted = Table.Pivot(Removed, List.Distinct(Removed[Attribute]), "Attribute", "NewCol")
in
PivotedPower Query solution 5 for Extract Entries from Running Total, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.FromColumns(
{Source[Date]}
& Table.ToColumns(
Table.FromRows(
List.Split(
List.Skip(
List.Accumulate(
List.Combine(List.Transform(Table.ToRows(Source), List.Skip)),
{0},
(s, c) => s & {c - List.Sum(s)}
)
),
3
)
)
),
Table.ColumnNames(Source)
)
in
AnswerSolving the challenge of Extract Entries from Running Total with Excel
Excel solution 1 for Extract Entries from Running Total, proposed by Rick Rothstein:
=LET(t,VSTACK(0,TOCOL(B3:D11)),VSTACK(A2:D2,HSTACK(A3:A11,WRAPROWS(DROP(t,1)-DROP(t,-1),3))))Excel solution 2 for Extract Entries from Running Total, proposed by John V.:
=HSTACK(A3:A11,B3:B11-N(+D2:D10),C3:D11-B3:C11)Excel solution 3 for Extract Entries from Running Total, proposed by محمد حلمي:
=IFNA(B3:D11-HSTACK(0,B3:C11),B3:B11-D2:D10)Excel solution 4 for Extract Entries from Running Total, proposed by محمد حلمي:
=IFNA(A3:D11-HSTACK(0,N(+D2:D10),B3:C11),A3:A11)Excel solution 5 for Extract Entries from Running Total, proposed by Kris Jaganah:
=VSTACK(A2:D2,HSTACK(A3:A11,WRAPROWS(TOCOL(B3:D11)-VSTACK(0,DROP(TOCOL(B3:D11),-1)),3)))Excel solution 6 for Extract Entries from Running Total, proposed by Julian Poeltl:
=LET(T,A2:D11,D,DROP(T,1,1),TC,TOCOL(D),HSTACK(TAKE(T,,1),VSTACK(TAKE(T,1,-3),WRAPROWS(VSTACK(TAKE(TC,1),DROP(DROP(TC,1)-TC,-1)),3))))Excel solution 7 for Extract Entries from Running Total, proposed by Timothée BLIOT:
=LET(
A,
TOCOL(
B3:D11
),
HSTACK(
A3:A11,
WRAPROWS(
VSTACK(
TAKE(
A,
1
),
DROP(
A,
1
)-DROP(
A,
-1
)
),
3
)
)
)Excel solution 8 for Extract Entries from Running Total, proposed by Sunny Baggu:
=LET(
a,
TOCOL(
B3:D11
),
HSTACK(
A3:A11,
WRAPROWS(
VSTACK(
B3,
DROP(
a,
1
) - DROP(
a,
-1
)
),
3
)
)
)Excel solution 9 for Extract Entries from Running Total, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,TOCOL(B3:D11),HSTACK(A3:A11,WRAPROWS(MMULT(MINVERSE(N(d>=TOROW(d))),d),3)))Excel solution 10 for Extract Entries from Running Total, proposed by Anshu Bantra:
=LET(
data_,
TOCOL(
B3:D11
),
tbl_,
WRAPROWS(
data_-VSTACK(
0,
DROP(
data_,
-1
)
),
3
),
VSTACK(
{"Date",
"Amt1",
"Amt2",
"Amt3"},
HSTACK(
A3:A11,
tbl_
)
)
)Excel solution 11 for Extract Entries from Running Total, proposed by Md. Zohurul Islam:
=LET(header,A2:D2,date,A3:A11,x,TOCOL(B3:D11),y,TOCOL(x-VSTACK(0,x),3),z,WRAPROWS(y,3),c,HSTACK(date,z),VSTACK(header,c))Excel solution 12 for Extract Entries from Running Total, proposed by Pieter de B.:
=LET(
x,
TOCOL(
B2:D11
),
HSTACK(
A2:A11,
WRAPROWS(
IFERROR(
x-VSTACK(
0,
DROP(
x,
-1
)
),
x
),
3
)
)
)Excel solution 13 for Extract Entries from Running Total, proposed by Hamidi Hamid:
=LET(
e,
B3:D11,
x,
HSTACK(
VSTACK(
@+e,
DROP(
-CHOOSECOLS(
e,
-1
)+VSTACK(
DROP(
TAKE(
e,
,
1
),
1
),
0
),
-1
)
),
DROP(
e,
,
1
)-DROP(
e,
,
-1
)
),
HSTACK(
A3:A11,
x
)
)Excel solution 14 for Extract Entries from Running Total, proposed by Asheesh Pahwa:
=LET(a,TOCOL(B3:D11),HSTACK(A3:A11,WRAPROWS(VSTACK(B3,DROP(a,1)-DROP(a,-1)),3)))Excel solution 15 for Extract Entries from Running Total, proposed by ferhat CK:
=HSTACK(A3:A11,WRAPROWS (TOCOL(B3:D11)-TAKE(VSTACK(0,TOCOL(B3:D11)),-1),3))Excel solution 16 for Extract Entries from Running Total, proposed by Ankur Sharma:
=HSTACK(A3:A11, VSTACK(B3, B4:B11 - D3:D10), C3:C11 - B3:B11, D3:D11 - C3:C11)Excel solution 17 for Extract Entries from Running Total, proposed by Imam Hambali:
=LET(
a, TOCOL(B3:D11),
b, TOCOL(a-VSTACK(0,a),3),
VSTACK(A2:D2,HSTACK(A3:A11, WRAPROWS(b,3)))
)Excel solution 18 for Extract Entries from Running Total, proposed by Mey Tithveasna:
=LET(
a,
A3:A11,
header,
A2:D2,
t,
TOCOL(
B3:D11
),
v,
VSTACK(
B3,
DROP(
t,
1
)-DROP(
t,
-1
)
),
VSTACK(
header,
HSTACK(
a,
WRAPROWS(
v,
3
)
)
)
)Excel solution 19 for Extract Entries from Running Total, proposed by Milan Shrimali:
=let(
a,
B2:B10,
b,
D2:D10,
c,
C2:C10,
aboverow,
BYROW(
b,
lambda(
x,
if(
row(
x
)=2,
index(
a,
1,
1
),
offset(
x,
-1,
0
)
)
)
),
d,
BYROW(
hstack(
a,
aboverow
),
lambda(
x,
abs(
choosecols(
x,
2
)-choosecols(
x,
1
)
)
)
),
firstcol,
arrayformula(
if(
d=0,
index(
a,
1,
1
),
d
)
),
lastcol,
BYROW(
hstack(
c,
b
),
lambda(
x,
aba(
choosecols(
x,
2
)-choosecols(
x,
1
)
)
)
),
midcol,
byrow(
hstack(
a,
c
),
lambda(
x,
abs(
choosecols(
x,
2
)-choosecols(
x,
1
)
)
)
),
hstack(
firstcol,
midcol,
lastcol
)
)Excel solution 20 for Extract Entries from Running Total, proposed by El Badlis Mohd Marzudin:
=LET(a,B3:B11,b,C3:C11,VSTACK(A2:D2,HSTACK(A3:A11,IFERROR(a-D2:D10,a),b-a,D3:D11-b)))Excel solution 21 for Extract Entries from Running Total, proposed by Songglod P.:
=LET(data,A2:D11,VSTACK(CHOOSEROWS(data,1),HSTACK(DROP(data,1,-3),LET(arr,TOCOL(B3:D11),WRAPROWS(arr-VSTACK(0,DROP(arr,-1)),3)))))Excel solution 22 for Extract Entries from Running Total, proposed by Marek Tomanek:
=LET(
data;
TOCOL(
B3:D11
);
numRws;
COUNTA(
data
);
nextRw;
CHOOSEROWS(
data;
SEQUENCE(
numRws-1;
;
2
)
);
out;
WRAPROWS(
CHOOSEROWS(
VSTACK(
CHOOSEROWS(
data;
1
);
nextRw-data
);
SEQUENCE(
numRws
)
);
3
);
out
)Solving the challenge of Extract Entries from Running Total with Python
Python solution 1 for Extract Entries from Running Total, proposed by Luke Jarych:
Python xlwings:
import pandas as pd
import xlwings as xw
from collections import Counter as ct
wb = xw.Book(r'Excel_Challenge_500 - Horizontal Running Total.xlsx')
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
df1 = pd.melt(df, id_vars=['Date'], var_name='Amt', value_name='Value').sort_values(by=['Date','Amt']).reset_index(drop=True)
df2 = df1.iloc[::-1]
df2['NewCol'] = df2['Value'].diff(-1).abs().fi&llna(df1['Value'].iloc[0]).astype(int)
df_result = df2.pivot(index='Date', columns='Amt', values='NewCol').reset_index()
Solving the challenge of Extract Entries from Running Total with Python in Excel
Python in Excel solution 1 for Extract Entries from Running Total, proposed by Abdallah Ally:
df = xl("A2:D11", headers=True)
# Perform data wrangling
columns = df.columns
df[1] = df['Amt1'].where(
cond=pd.isna(df['Amt1'].shift(1)),
other=df['Amt1']-df['Amt3'].shift(1)
)
df[2] = df['Amt2']-df['Amt1']
df[3] = df['Amt3']-df['Amt2']
df = df.filter(regex='^[^A]', axis=1)
df.columns = columns
df
Python in Excel solution 2 for Extract Entries from Running Total, proposed by Anshu Bantra:
df = xl("A2:D11", headers=True)
df1 = pd.melt(df, id_vars=['Date'], var_name='Amt', value_name='Value').sort_values(by=['Date','Amt']).reset_index(drop=True)
lst = [df1.loc[_-1,'Value']-df1.loc[_-2,'Value'] for _ in range(len(df1),1,-1)]
lst.append(df1.loc[0,'Value'])
df1['Value'] = lst[::-1]
df1 = df1.pivot( index='Date', columns='Amt', values='Value' )
df1
Python in Excel solution 3 for Extract Entries from Running Total, proposed by ferhat CK:
a=np.array(xl("B3:D11"))
b=a.reshape(1,a.size)
d=np.insert(b,0,0)
e=b-d[:-1]
f=e.reshape(9,3)
pd.DataFrame({"Date":xl("A3:A11").values.flatten(),"Amt1":f[:,0],"Amt2":f[:,1],"Amt3":f[:,2]})
Solving the challenge of Extract Entries from Running Total with R
R solution 1 for Extract Entries from Running Total, proposed by Anil Kumar Goyal:
df <- read_excel("Excel/Excel_Challenge_500 - Horizontal Running Total.xlsx", range = cell_cols("A:D"))
df %>%
mutate(across(3:4, ~ . - get(paste0(
"Amt", parse_number(cur_column()) - 1
)))) %>%
mutate(Amt1 = Amt1 - lag(df$Amt3, default = 0))
&&