The customer receipt costs and their payments are provided in Question Tables 1 and 2. We want to match the payments with the receipts based on the date, as shown in the result table. Receipts that are not paid will be marked as “NP” (Not Paid). For example, Payment ID P1 is used to pay for receipts C1 to C3.
📌 Challenge Details and Links
Challenge Number: 60
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Match Payments! with Power Query
Power Query solution 1 for Match Payments!, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T1 = Source("Table1"),
T2 = Source("Table2"),
S = Table.FromRows(
List.TransformMany(
Table.ToRows(T1),
each
let
r = List.Sum(List.FirstN(T1[Cost], List.PositionOf(T1[ID], _{0}))) ?? 0,
p = T2[Payment]
in
{
List.Accumulate(
List.Positions(p),
{},
(s, c) =>
s
& {
let
i = List.Sum(s) ?? 0,
pp = List.Sum(List.FirstN(p, c)) ?? 0
in
{
{null, List.Min({pp - r + p{c} - i, _{2} - i})}{
Number.From(pp - r + p{c} - i > 0)
},
null
}{Number.From(_{2} = i)}
}
)
},
(i, _) =>
{i{0}} & {List.Repeat({"NP"}, List.Count(_)), _}{Number.From(List.NonNullCount(_) > 0)}
),
{" "} & T2[ID]
)
in
SPower Query solution 2 for Match Payments!, proposed by Rafael González B.:
let
Rec = Recipts, Pay = Payments, R = "Running",
Fx_RunTotal = (L as list, T as table) =>
let
aa = List.Generate(
() => [i = 1, Acum = L{0} ],
each [i] <= List.Count(L),
each [
i = [i] + 1,
Acum = [Acum] + L{[i]}],
each [Acum]),
bb = Table.ToColumns(T),
cc = Table.FromColumns(bb & {aa}, Table.ColumnNames(T) & {R})
in
cc,
Rec2 = let
a = Table.AddColumn(Rec, "Unit", each List.Repeat({1}, [Cost])),
b = Table.ExpandListColumn(a, "Unit")
in
b,
Power Query solution 3 for Match Payments!, proposed by Aditya Kumar Darak 🇮🇳:
let
Receipts = Excel.CurrentWorkbook(){[Name = "receipts"]}[Content],
Payments = Excel.CurrentWorkbook(){[Name = "payments"]}[Content],
Generate = List.Generate(
() => [n1 = - 1, n2 = - 1, rr = 0, rp = 0],
each [n1] < Table.RowCount(Receipts) and [n2] < Table.RowCount(Payments),
each [
n1 = [n1] + Number.From(t1),
n2 = [n2] + Number.From(t2),
r = Receipts{n1},
ri = r[ID],
rc = r[Cost],
p = Payments{n2},
pi = p[ID],
pp = p[Payment],
t1 = [rr] = 0,
t2 = [rp] = 0,
AU = List.Min({if t2 then pp else [rp], if t1 then rc else [rr]}),
rr = if t1 then rc - AU else [rr] - AU,
rp = if t2 then pp - AU else [rp] - AU
],
each [ID = [ri], P = [pi], A = [AU]]
),
Table = Table.FromRecords(List.Skip(Generate)),
Pivot = Table.Pivot(Table, Payments[ID], "P", "A", each _{0}? ?? ""),
Join = Table.AddJoinColumn(Receipts[[ID]], {"ID"}, Pivot, {"ID"}, "J"),
Expand = Table.ExpandTableColumn(Join, "J", Payments[ID]),
Replace = Table.ReplaceValue(Expand, null, "NP", (x, y, z) => x ?? z, Payments[ID]),
Return = Table.Sort(Replace, each List.PositionOf(Receipts[ID], [ID]))
in
ReturnPower Query solution 4 for Match Payments!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Combine(Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "A", each List.Repeat({[ID]}, [Cost]))[A]),
Tbl2 = List.Combine(Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table2"]}[Content], "A", each List.Repeat({[ID]}, [Payment]))[A]),
Tbl = Table.FromColumns({Source, Tbl2}),
Group = Table.Group(Tbl, {"Column1"}, {{"A", each
let
a = _,
b = Table.Group(a, "Column2", {{"B", each List.Count([Column1])}}),
c = Table.SelectRows(b, each [Column2]<>null),
d = Table.PromoteHeaders(Table.Transpose(c)),
e = if Table.IsEmpty(d) then Table.FromRows({List.Repeat({"NP"},
List.Count(List.Distinct(Tbl2)))}, List.Distinct(Tbl2)) else d
in e}}),
Sol = Table.ExpandTableColumn(Group, "A", List.Distinct(Tbl2))
in
SolSolving the challenge of Match Payments! with Excel
Excel solution 1 for Match Payments!, proposed by Bo Rydobon 🇹🇭:
=LET(d,
DROP(
REDUCE(
B3:B14,
H3:H7,
LAMBDA(
b,
w,
HSTACK(
b,
DROP(
REDUCE(
w,
D3:D14-BYROW(
b,
SUM
),
LAMBDA(
a,
v,
VSTACK(
a,
MIN(
@a*2-SUM(
a
),
v
)
)
)
),
1
)
)
)
),
,
0
),VSTACK(TOROW(
F2:F7
),
IF(BYROW(
d,
SUM
)+(d>""),
d,
"NP")))Excel solution 2 for Match Payments!, proposed by محمد حلمي:
=LET(
s,
SEQUENCE(
5
), i,
CHOOSECOLS( REDUCE(
D3:D14,
H3:H7,
LAMBDA(
a,
w,
LET(
v,
TAKE(
a,
,
-1
),
e,
DROP(
SCAN(
,
VSTACK(
w,
v
),
LAMBDA(
a,
v,
a-v
)
),
-1
),
HSTACK(
a,
IFS(
e>v,
v,
e>0,
e,
1,
),
IFS(
e>v,
0,
e<0,
v,
1,
v-e
)
)
)
)
),
s*2
), IF(
MMULT(
i,
s^0
),
i,
"NP"
)
)Excel solution 3 for Match Payments!, proposed by Julian Poeltl:
=LET(T,
B3:D14,
TT,
F3:H7,
RT,
SCAN(
,
TAKE(
T,
,
-1
),
LAMBDA(
A,
B,
A+B
)
),
RTT,
SCAN(
,
TAKE(
TT,
,
-1
),
LAMBDA(
A,
B,
A+B
)
),
RC,
ROWS(
T
),
CP,
ROWS(
TT
),
FP,
XMATCH(
RT,
RTT,
1
),
Gap,
--(FP=VSTACK(
1,
FP
)),
FullP,
IF(
Gap,
TAKE(
T,
,
-1
),
""
),
SR,
SEQUENCE(
RC
),
CPS,
SEQUENCE(
,
CP
),
C,
ABS(
IF(
RT>0,
INDEX(
RTT,
FP-1
)
)-RT
),
CC,
IF(
FullP="",
C,
FullP
),
Field1,
XLOOKUP(
SR&CPS,
SR&FP,
DROP(
CC,
-1
),
0
),
BRS,
TRANSPOSE(
TAKE(
TT,
,
-1
)
)-BYCOL(
Field1,
LAMBDA(
A,
SUM(
A
)
)
),
CCC,
--(Field1>0)*SR,
BCM,
BYCOL(
CCC,
LAMBDA(
A,
MAX(
A
)
)
),
CCCC,
IF(
BCM>0,
BCM+1
),
SC,
SCAN(
,
CCCC,
LAMBDA(
A,
B,
IF(
B=FALSE,
A,
B
)
)
),
Field2,
IF(
SR*SEQUENCE(
,
ROWS(
TT
),
,
0
)=SC,
BRS,
0
),
R,
Field1+Field2,
M,
MIN(
IFERROR(
DROP(
CC,
-1
),
SR
)
)-1,
RR,
VSTACK(
HSTACK(
"",
TRANSPOSE(
TAKE(
TT,
,
1
)
)
),
HSTACK(
TAKE(
T,
,
1
),
EXPAND(
TAKE(
R,
M
),
RC,
,
"NP"
)
)
),
IF(
RR<>0,
RR,
""
))Excel solution 4 for Match Payments!, proposed by John Jairo Vergara Domínguez:
=LET(
c,
D3:D14,
h,
HSTACK,
v,
VSTACK,
n,
DROP(
REDUCE(
0*c,
H3:H7,
LAMBDA(
b,
w,
h(
b,
DROP(
REDUCE(
0,
c-BYROW(
b,
SUM
),
LAMBDA(
a,
z,
v(
a,
MIN(
w-SUM(
a
),
z
)
)
)
),
1
)
)
)
),
,
1
),
v(
h(
"",
TOROW(
F3:F7
)
),
h(
B3:B14,
IF(
BYROW(
n,
SUM
),
n,
"NP"
)
)
)
)Excel solution 5 for Match Payments!, proposed by John Jairo Vergara Domínguez:
=LET(f,
VSTACK,
n,
REDUCE(
B3:B14,
-H3:H7,
LAMBDA(
i,
x,
HSTACK(
i,
DROP(
REDUCE(
x,
BYROW(
i,
SUM
)-D3:D14,
LAMBDA(
a,
v,
f(
a,
-MAX(
v,
SUM(
a
)
)
)
)
),
1
)
)
)
),
f(TOROW(
F2:F7
),
IF(BYROW(
n,
SUM
)+(n>0),
n,
"NP")))Excel solution 6 for Match Payments!, proposed by Hussein SATOUR:
=LET(
d,
ROW(
1:12
),
e,
J3:J7,
f,
LAMBDA(
x,
y,
TEXTSPLIT(
CONCAT(
REPT(
x&"|",
y
)
),
,
"|",
1
)
),
a,
--f(
d,
D3:D14
),
b,
f(
H3:H7,
e
),
c,
--f(
1,
e
),
IFNA(
HSTACK(
VSTACK(
"",
"C"&d
),
DROP(
PIVOTBY(
TAKE(
a,
SUM(
e
)
),
b,
c,
SUM,
,
0,
,
0
),
,
1
)
),
"NP"
)
)Excel solution 7 for Match Payments!, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(c,
D3:D14,
p,
H3:H7, rc,
ROWS(
c
),
rp,
ROWS(
p
), cz,
SEQUENCE(
rc,
,
0,
0
), pz,
SEQUENCE(
,
rp,
0,
0
), cx,
MAKEARRAY(
rc,
rp,
LAMBDA(
x,
y,
INDEX(
c,
x
)
)
), cs,
MAKEARRAY(
rc,
rp,
LAMBDA(
x,
y,
SUM(
TAKE(
c,
x
)
)
)
), ps,
MAKEARRAY(
rc,
rp,
LAMBDA(
x,
y,
SUM(
TAKE(
p,
y
)
)
)
), csd,
VSTACK(
pz,
DROP(
cs,
-1
)
), a,
IF(csd0,
cx,
ps-csd),
0), b,
HSTACK(
DROP(
a,
,
1
),
cz
), r,
HSTACK(
TAKE(
a,
,
1
),
DROP(
b-a,
,
-1
)
), IF(
BYROW(
r,
SUM
)=0,
"NP",
IF(
r=0,
"",
r
)
)
) Solving the challenge of Match Payments! with R
R solution 1 for Match Payments!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input1 = read_excel("files/CH-060 Match payments.xlsx", range = "B2:D14")
input2 = read_excel("files/CH-060 Match payments.xlsx", range = "F2:H7")
test = read_excel("files/CH-060 Match payments.xlsx", range = "K2:P14")
rec = input1 %>%
mutate(ID = factor(ID, levels = paste0("C", 1:12), ordered = TRUE)) %>%
uncount(Cost, .remove = FALSE) %>%
mutate(Cost = 1, rn = row_number())
pay = input2 %>%
mutate(ID = factor(ID, levels = paste0("P", 1:5), ordered = TRUE)) %>%
uncount(Payment, .remove = FALSE) %>%
mutate(Payment = 1, rn = row_number())
all = full_join(rec, pay, by = "rn") %>%
summarise(Value = sum(Payment), .by = c("ID.x", "ID.y")) %>%
pivot_wider(names_from = "ID.y", values_from = "Value") %>%
select(-"NA") %>%
mutate(across(everything(), as.character))
R solution 2 for Match Payments!, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
rec <- read_excel("OM Challanges/CH-060 Match payments.xlsx",
range = "B2:D14")
pymt <- read_excel("OM Challanges/CH-060 Match payments.xlsx",
range = "F2:H7")
rec %>%
mutate(cum_r = cumsum(Cost),
cum_r_prev = lag(cum_r, default = 0)) %>%
left_join(
pymt %>%
mutate(cum_p = cumsum(Payment),
cum_p_prev = lag(cum_p, default = 0)),
by = join_by(overlaps(cum_r_prev, cum_r, cum_p_prev, cum_p, bounds = "()"))
) %>%
mutate(ID.x, ID.y,
matching = pmin(cum_r, cum_p),
matching = c(first(matching), diff(matching)),
.keep = "none") %>%
fill(ID.y) %>%
pivot_wider(id_cols = ID.x, names_from = ID.y, values_from = matching,
values_fill = 0)