After reconciling the company’s bank transactions with the records in the financial department, discrepancies were found between 5 rows of bank transactions and 7 rows of financial records. This indicates that a single bank transaction might have been split into multiple entries in the financial records, or vice versa. Extract all the possible matching scenarios of bank transactions with a or combination of financial records, ensuring that:-Each transaction is used only once. 👉Transactions cannot be split. 👉Exclude any scenarios where multiple rows of bank transactions match with multiple rows of financial records. Matching records in the first scenario are highlighted with the same color.
📌 Challenge Details and Links
Challenge Number: 110
Challenge Difficulty: ⭐⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reconciliation! with Excel
Excel solution 1 for Reconciliation!, proposed by Bo Rydobon 🇹🇭:
=LET(
L,
LAMBDA(
a,
x,
DROP(
REDUCE(
IF(
x,
"",
0
),
a,
LAMBDA(
c,
v,
VSTACK(
c,
IF(
x,
c&"+"&v,
c+v
)
)
)
),
1
)
), bi,
B3:B7,
Lbi,
MID(
L(
bi,
1
),
2,
99
),
Lbv,
L(
D3:D7, ),
Lfi,
MID(
L(
F3:F9,
1
),
2,
99
),
Lfv,
L(
H3:H9, ), c,
DROP(
REDUCE(
0,
UNIQUE(
Lbv
),
LAMBDA(
a,
v,
LET(
f,
FILTER(
Lfi,
Lfv=v,
0
),
IF(
@f>0,
VSTACK(
a,
FILTER(
Lbi,
Lbv=v
)&"="&f
),
a
)
)
)
),
1
), B,
LAMBDA(
b,
a,
LET(
n,
ROWS(
a
),
IF(
n=1,
LET(
f,
FILTER(
c,
MAP(
c,
LAMBDA(
d,
AND(
ISERR(
FIND(
TEXTSPLIT(
@a,
{"=",
"+",
", "}
),
d
)
)
)
)
),
0
),
IF(
@f>0,
MAP(
a&", "&f,
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
TEXTSPLIT(
x,
,
", "
)
)
)
)
),
a
)
),
UNIQUE(
VSTACK(
b(
b,
TAKE(
a,
n/2
)
),
b(
b,
DROP(
a,
n/2
)
)
)
)
)
)
), REDUCE(
c,
bi,
LAMBDA(
x,
i,
B(
B,
x
)
)
)
)Excel solution 2 for Reconciliation!, proposed by Bo Rydobon 🇹🇭:
=LET(L,
LAMBDA(
a,
[x],
DROP(
REDUCE(
IF(
x,
"",
0
),
a,
LAMBDA(
c,
v,
VSTACK(
c,
IF(
x,
c&"+"&v,
c+v
)
)
)
),
1
)
),bi,
B3:B7,
bv,
D3:D7,
fi,
F3:F9,
fv,
H3:H9,
Lfi,
L(
fi,
1
),
Lfv,
L(
fv, ),G,
LAMBDA(
G,
a,
i,
LET(
n,
ROWS(
a
),
IF(
n=1,
LET(
y,
ISERROR(
XMATCH(
fi,
TOCOL(
DROP(
TEXTSPLIT(
@a,
{"+",
"="},
", "
),
,
1
)
)
)
),
f,
FILTER(
L(
FILTER(
fi,
y
),
1
),
L(
FILTER(
fv,
y
),
0
)=XLOOKUP(
i,
bi,
bv
),
0
),
IF(
@f>0,
a&", "&SUBSTITUTE(
i&f,
"+",
"=",
1
),
a
)
),
VSTACK(
G(
G,
TAKE(
a,
n/2
),
i
),
G(
G,
DROP(
a,
n/2
),
i
)
)
)
)
),fc,
MID(
REDUCE(
"",
bi,
LAMBDA(
a,
i,
G(
G,
a,
i
)
)
),
3,
999
),MAP(fc,
LAMBDA(v,
LET(t,
TEXTSPLIT(
v,
{"=",
"+"},
", ",
1
),
F,
LAMBDA(x,
LET(a,
IF(
x,
bi,
fi
),
TEXTJOIN("+",
,
FILTER(a,
ISNA(XMATCH(a,
TOCOL(IF(
x,
TAKE,
DROP
)(t,
,
1),
3))))))),TEXTJOIN(
", ",
,
SORT(
VSTACK(
TEXTSPLIT(
v,
,
", "
),
F(
1
)&"="&F(
0
)
)
)
)))))Excel solution 3 for Reconciliation!, proposed by محمد حلمي:
=LET(
w,
IFNA(
DROP(
REDUCE(
0,
D3:D7,
LAMBDA(
a,
v,
HSTACK(
a,
OFFSET(
v,
,
-2
)&"="&MID(
FILTER(
REDUCE(
"",
F3:F9,
LAMBDA(
q,
w,
VSTACK(
q,
q&"+"&w
)
)
),
REDUCE(
0,
H3:H9,
LAMBDA(
a,
v,
VSTACK(
a,
a+v
)
)
)=v
),
2,
99
)
)
)
),
,
1
),
""
), q,
UNIQUE(
MID(
REDUCE(
"",
SEQUENCE(
COLUMNS(
w
)
),
LAMBDA(
a,
v,
LET(
x,
INDEX(
w,
,
v
),
VSTACK(
a,
TOCOL(
a&", "&
SORT(
TOROW(
FILTER(
x,
x>""
)
),
,
,
1
),
2
)
)
)
)
),
3,
150
)
),
s,
LEN(
q
)-LEN(
SUBSTITUTE(
q,
"=",
)
),
FILTER(
q,
s=ROWS(
B3:B7
)
)
)Excel solution 4 for Reconciliation!, proposed by محمد حلمي:
=DROP(
TOCOL(
REDUCE(
0,
D3:D7,
LAMBDA(
a,
v,
HSTACK(
a,
OFFSET(
v,
,
-2
)&"="&MID(
FILTER(
REDUCE(
"",
F3:F9,
LAMBDA(
q,
w,
VSTACK(
q,
q&"+"&w
)
)
),
REDUCE(
0,
H3:H9,
LAMBDA(
a,
v,
VSTACK(
a,
a+v
)
)
)=v
),
2,
99
)
)
)
),
2
),
1
)Excel solution 5 for Reconciliation!, proposed by Julian Poeltl:
=LET(V,
D3:D7,
VV,
H3:H9,
R,
ROWS(
V
),
RV,
ROWS(
VV
),
SP,
LAMBDA(
A,
VSTACK(
SEQUENCE(
A
),
L_P(
A
)
)
),
M,
LAMBDA(
A,
B,
MAP(
A,
LAMBDA(
A,
SUM(
INDEX(
B,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
)
)
)
),
SRV,
SP(
RV
),
SR,
SP(
R
),
FM,
SRV&"="&TRANSPOSE(
SR
),
T,
TOCOL(
IF(
M(
SRV,
VV
)=TRANSPOSE(
M(
SR,
V
)
),
FM,
n
),
3
),
F,
FILTER(T,
(LEN(
TEXTBEFORE(
T,
"="
)
)=1)+(LEN(
TEXTAFTER(
T,
"="
)
)=1)),
U,
UNIQUE(
MAP(
F,
LAMBDA(
A,
LET(
B,
TEXTBEFORE(
A,
"="
),
T,
TEXTAFTER(
A,
"="
),
CONCAT(
SORT(
MID(
B,
SEQUENCE(
LEN(
B
)
),
1
)
),
"=",
SORT(
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
)
)
)
)
)
)
),
C,
MAP(
U,
LAMBDA(
A,
LET(
B,
TEXTBEFORE(
A,
"="
),
T,
TEXTAFTER(
A,
"="
),
TEXTJOIN(
"+",
,
"B"&MID(
T,
SEQUENCE(
LEN(
T
)
),
1
)
)&"="&TEXTJOIN(
"+",
,
"F"&MID(
B,
SEQUENCE(
LEN(
B
)
),
1
)
)
)
)
),
CO,
MAP(
C,
LAMBDA(
A,
SUM(
XLOOKUP(
TEXTSPLIT(
TEXTBEFORE(
A,
"="
),
"+"
),
B3:B7,
V
)
)
)
),
SC,
SORTBY(
C,
CO
),
PT,
L_P(
9,
3
),
UPT,
UNIQUE(
MAP(
PT,
LAMBDA(
A,
CONCAT(
SORT(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
)
)
)
),
DS,
DROP(
SC,
-3
),
FS,
FILTER(UPT,
(MAP(
MAP(
UPT,
LAMBDA(
A,
TEXTJOIN(
"+",
,
INDEX(
TEXTAFTER(
DS,
"="
),
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
"F6"
)
)
),
LAMBDA(
A,
ROWS(
UNIQUE(
TEXTSPLIT(
A,
,
"+"
)
)
)
)
)=7)*(MAP(
MAP(
UPT,
LAMBDA(
A,
TEXTJOIN(
"+",
,
INDEX(
TEXTBEFORE(
DS,
"="
),
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
"B3",
"B4"
)
)
),
LAMBDA(
A,
ROWS(
UNIQUE(
TEXTSPLIT(
A,
,
"+"
)
)
)
)
)=5)),
MAP(
MAP(
FS,
LAMBDA(
A,
TEXTJOIN(
", ",
,
INDEX(
DS,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
TAKE(
SC,
-1
)
)
)
),
LAMBDA(
A,
TEXTJOIN(
", ",
,
SORT(
TEXTSPLIT(
A,
,
", "
)
)
)
)
))Excel solution 6 for Reconciliation!, proposed by Nonbow Wu:
=LET(
da,
CHOOSECOLS(
B3:D7,
1,
3
),
db,
CHOOSECOLS(
F3:H9,
1,
3
), s2a,
LAMBDA(
s,
MID(
s,
SEQUENCE(
LEN(
s
)
),
1
)
), cb,
LAMBDA(
n,
DROP(
REDUCE(
"",
SEQUENCE(
,
n
),
LAMBDA(
a,
i,
VSTACK(
a,
TOCOL(
IFS(
ISERR(
FIND(
i,
a
)
),
a&i
),
2
)
)
)
),
1
)
), pn,
LAMBDA(
a,
b,
TOCOL(
MAP(
cb(
ROWS(
b
)
),
LAMBDA(
v,
IFS(
SUM(
INDEX(
b,
s2a(
v
),
1
)
)=a,
v
)
)
),
2
)
), foo,
LAMBDA(
aa,
bb,
[e],
MAP(
TAKE(
aa,
,
1
),
TAKE(
aa,
,
-1
),
LAMBDA(
a,
x,
LET(
k,
MAP(
pn(
x,
TAKE(
bb,
,
-1
)
),
LAMBDA(
c,
REPT(
a&"=",
e=0
)&TEXTJOIN(
"+",
,
INDEX(
TAKE(
bb,
,
1
),
s2a(
c
),
1
)
)&REPT(
"="&a,
e<>0
)
)
),
ARRAYTOTEXT(
k
)
)
)
)
), t,
UNIQUE(
TEXTSPLIT(
ARRAYTOTEXT(
TOCOL(
VSTACK(
foo(
da,
db
),
foo(
db,
da,
1
)
),
2
)
),
,
", "
)
), ta,
DROP(
REDUCE(
"",
UNIQUE(
TEXTBEFORE(
t,
"="
)
),
LAMBDA(
a,
v,
VSTACK(
a,
TOCOL(
a&", "&TOROW(
IFS(
ISNUMBER(
FIND(
v,
t
)
),
t
),
2
)
)
)
)
),
1
), tb,
MAP(
ta,
LAMBDA(
a,
ARRAYTOTEXT(
SORT(
UNIQUE(
TEXTSPLIT(
a,
,
",",
1
)
)
)
)
)
), id,
TAKE(
VSTACK(
da,
db
),
,
1
), UNIQUE(
TOCOL(
MAP(
tb,
LAMBDA(
v,
IFS(
AND(
LEN(
v
)-LEN(
SUBSTITUTE(
v,
id,
)
)=LEN(
id
)
),
v
)
)
),
2
)
)
)