Prepare the cross product of Column1 with Column2 where an entry should be repeated by the number in brackets.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 585
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Cross Product with Repeats with Power Query
Power Query solution 1 for Cross Product with Repeats, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (v) =>
List.Combine(
List.Transform(
List.RemoveNulls(Table.ToColumns(A){v}),
each Text.ToList(
Text.Repeat(Text.Select(_, {"A" .. "Z"}), Number.From(Text.Select(_, {"0" .. "9"})))
)
)
),
C = List.TransformMany(B(0), each B(1), (x, y) => x & y)
in
CPower Query solution 2 for Cross Product with Repeats, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Listas = List.Transform(
Table.ToColumns(Source),
(x) =>
List.Combine(
List.Transform(
List.RemoveNulls(x),
each
let
a = Splitter.SplitTextByCharacterTransition({"A" .. "Z"}, {"0" .. "9"})(
Text.Remove(_, {"(", ")"})
),
b = List.Repeat({a{0}}, Number.From(a{1}))
in
b
)
)
),
Sol = List.TransformMany(Listas{0}, each Listas{1}, (x, y) => x & y)
in
SolPower Query solution 3 for Cross Product with Repeats, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
Table.ToColumns(Source),
each List.Combine(
List.Transform(
List.RemoveNulls(_),
(x) =>
List.Repeat(
{Text.BeforeDelimiter(x, "(")},
Number.From(Text.BetweenDelimiters(x, "(", ")"))
)
)
)
),
Result = List.TransformMany(Transform{0}, each Transform{1}, (x, y) => x & y)
in
ResultSolving the challenge of Cross Product with Repeats with Excel
Excel solution 1 for Cross Product with Repeats, proposed by Bo Rydobon 🇹🇭:
=LET(L,LAMBDA(a,LET(c,CONCAT(REPT(LEFT(a),-MID(a,2,9))),MID(c,SEQUENCE(LEN(c)),1))),TOCOL(L(A2:A5)&TOROW(L(B2:B4))))Excel solution 2 for Cross Product with Repeats, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
"",
A2:B2,
LAMBDA(
a,
v,
LET(
b,
TOCOL(
TAKE(
v:B5,
,
1
),
3
),
c,
TEXTSPLIT(
CONCAT(
b
),
"(",
")",
1
),
n,
--DROP(
c,
,
1
),
d,
SCAN(
,
n,
SUM
)-n,
TOCOL(
a&LOOKUP(
SEQUENCE(
,
SUM(
n
),
0
),
d,
TAKE(
c,
,
1
)
)
)
)
)
)Excel solution 3 for Cross Product with Repeats, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
"",
A2:B2,
LAMBDA(
a,
v,
LET(
b,
TOCOL(
TAKE(
v:B5,
,
1
),
3
),
c,
TEXTSPLIT(
CONCAT(
b
),
"(",
")",
1
),
n,
--DROP(
c,
,
1
),
d,
SCAN(
,
n,
SUM
)-n,
TOCOL(
a&LOOKUP(
SEQUENCE(
,
SUM(
n
),
0
),
d,
TAKE(
c,
,
1
)
)
)
)
)
)Excel solution 4 for Cross Product with Repeats, proposed by Rick Rothstein:
=LET(
f,
LAMBDA(
r,
LET(
m,
-MID(
r,
FIND(
"(",
r
),
9
),
TEXTSPLIT(
TRIM(
CONCAT(
REPT(
TEXTBEFORE(
r,
"("
)&" ",
m
)&" "
)
),
,
" "
)
)
),
TOCOL(
f(
A2:A5
)&TOROW(
f(
B2:B4
)
)
)
)Excel solution 5 for Cross Product with Repeats, proposed by John V.:
=LET(
f,
LAMBDA(
r,
LET(
n,
--MID(
r,
3,
1
),
TOCOL(
IFS(
n>=SEQUENCE(
,
MAX(
n
)
),
LEFT(
r
)
),
2
)
)
),
TOCOL(
f(
A2:A5
)&TOROW(
f(
B2:B4
)
)
)
)Excel solution 6 for Cross Product with Repeats, proposed by 🇰🇷 Taeyong Shin:
=LET(F,LAMBDA(f,x,LET(l,LEFT(x),n,-NUMBERVALUE(x," ",l),f(REPT(l,1/(SEQUENCE(,MAX(n))<=n)),2))),TOCOL(F(TOCOL,A2:A5)&F(TOROW,B2:B4)))Excel solution 7 for Cross Product with Repeats, proposed by Kris Jaganah:
=LET(
a,
LAMBDA(
x,
CONCAT(
REPT(
LEFT(
x
),
-RIGHT(
x,
3
)
)
)
),
b,
a(
A2:A5
),
c,
a(
TOROW(
B2:B5,
3
)
),
d,
SEQUENCE,
TOCOL(
MID(
b,
d(
LEN(
b
)
),
1
)&MID(
c,
d(
,
LEN(
c
)
),
1
)
)
)Excel solution 8 for Cross Product with Repeats, proposed by Julian Poeltl:
=LET(
C,
A2:A5,
CC,
B2:B4,
L,
LAMBDA(
X,
DROP(
REDUCE(
0,
X,
LAMBDA(
A,
B,
VSTACK(
A,
IF(
SEQUENCE(
TEXTBEFORE(
TEXTAFTER(
B,
"("
),
")"
)
),
TEXTBEFORE(
B,
"("
)
)
)
)
),
1
)
),
TOCOL(
L(
C
)&TOROW(
L(
CC
)
)
)
)Excel solution 9 for Cross Product with Repeats, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_Col_1, A2:A5,
_Col_2, B2:B4,
_Col1V, LEFT(_Col_1),
_Col1T, -MID(_Col_1, 2, 9),
_Col2V, LEFT(_Col_2),
_Col2T, -MID(_Col_2, 2, 9),
_FCol1, TOCOL(IFS(SEQUENCE(1, MAX(_Col1T)) <= _Col1T, _Col1V), 2),
_FCol2, TOCOL(IFS(SEQUENCE(1, MAX(_Col2T)) <= _Col2T, _Col2V), 2),
_Result, TOCOL(_FCol1 & TOROW(_FCol2)),
_Result
)Excel solution 10 for Cross Product with Repeats, proposed by Oscar Mendez Roca Farell:
=LET(F, LAMBDA(i, LET(m,--MID(i,3,1), TOCOL(IFS(m>=SEQUENCE(,MAX(m)), LEFT(i)),2))), TOCOL(F(A2:A5)&TOROW(F(B2:B4))))Excel solution 11 for Cross Product with Repeats, proposed by Duy Tùng:
=LET(
h,
LAMBDA(
v,
TEXTSPLIT(
CONCAT(
REPT(
LEFT(
v
)&"/",
MID(
v,
3,
1
)
)
),
,
"/",
1
)
),
TOCOL(
h(
A2:A5
)&TOROW(
h(
B2:B4
)
)
)
)Excel solution 12 for Cross Product with Repeats, proposed by Sunny Baggu:
=LET(
_c1, IFNA(TEXTBEFORE(TEXTAFTER(A2:B5, "("), ")"), ""),
_c2, TEXTBEFORE(A2:B5, "(", , , , ""),
DROP(
REDUCE(
"",
SEQUENCE(ROWS(A2:A5)),
LAMBDA(a, v,
VSTACK(
a,
LET(
_a, INDEX(TAKE(_c1, , 1), v, 1),
_b, INDEX(TAKE(_c2, , 1), v, 1),
TOCOL(
IF(
SEQUENCE(, _a),
TEXTSPLIT(ARRAYTOTEXT(TOCOL(REPT("," & _b & TAKE(_c2, , -1), TAKE(_c1, , -1)), 3)), , {", ", ","}, 1)
),
,
1
)
)
)
)
),
1
)
)Excel solution 13 for Cross Product with Repeats, proposed by Sunny Baggu:
=LET(
t,
A2:B5,
_n,
IFERROR(
MID(
t,
1 + SEARCH(
"(",
t
),
1
),
""
),
_c,
IF(
LEFT(
t
) = "",
1 / x,
LEFT(
t
)
),
_d,
TAKE(
_c,
,
1
) & TOROW(
TAKE(
_c,
,
-1
),
3
),
DROP(
TEXTSPLIT(
CONCAT(
MAP(
SEQUENCE(
ROWS(
_n
)
),
LAMBDA(
a,
CONCAT(
IF(
SEQUENCE(
INDEX(
--TAKE(
_n,
,
1
),
a,
1
)
),
HSTACK(
REPT(
"," & INDEX(
_d,
a,
),
TOROW(
--TAKE(
_n,
,
-1
),
3
)
)
)
)
)
)
)
),
,
{",",
", "}
),
1
)
)Excel solution 14 for Cross Product with Repeats, proposed by LEONARD OCHEA 🇷🇴:
=LET(a,A2:A5,b,B2:B4,E,REGEXEXTRACT,F,LAMBDA(x,E(CONCAT(REPT(E(x,"w"),E(x,"d"))),".",1)),TOCOL(TOCOL(F(a))&F(b)))Excel solution 15 for Cross Product with Repeats, proposed by Abdallah Ally:
=LET(f,LAMBDA(x,DROP(REDUCE("",x,LAMBDA(u,v,VSTACK(u,LET(a, TEXTBEFORE(v,"("),EXPAND(a,REGEXEXTRACT(v,"d+"),,a))))),1)), TOCOL(f(A2:A5)&TOROW(f(B2:B4))))Excel solution 16 for Cross Product with Repeats, proposed by Hamidi Hamid:
=LET(q,LAMBDA(o,p,TAKE(TEXTBEFORE(o,"("),,p)),x,IFERROR(TEXTAFTER(TEXTBEFORE(A2:B5,")",),"(",)*1,0),y,q(A2:B5,1),z,IFERROR(q(A2:B5,-1),""),rd,REDUCE(0,TAKE(BYCOL(REPT(y,x),CONCAT),,1),LAMBDA(a,b,MID(b,SEQUENCE(LEN(b)),1))),rb,REDUCE(0,TAKE(BYCOL(REPT(HSTACK(y,z),x),CONCAT),,-1),LAMBDA(a,b,MID(b,SEQUENCE(,LEN(b)),1))),dc,TOCOL(rd&rb),dc)Excel solution 17 for Cross Product with Repeats, proposed by Asheesh Pahwa:
=LET(
rd,
DROP(
REDUCE(
"",
A2:A5,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
--TEXTBEFORE(
TEXTAFTER(
y,
"("
),
")"
& ),
s,
ROUNDUP(
SEQUENCE(
t
)/t,
0
),
l,
LEFT(
y
),
r,
REPT(
l,
s
),
r
)
)
)
),
1
),
_r,
DROP(
REDUCE(
"",
B2:B4,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
--TEXTBEFORE(
TEXTAFTER(
y,
"("
),
")"
),
s,
ROUNDUP(
SEQUENCE(
t
)/t,
0
),
l,
LEFT(
y
),
REPT(
l,
s
)
)
)
)
),
1
),
DROP(
REDUCE(
"",
rd,
LAMBDA(
z,
p,
VSTACK(
z,
p&_r
)
)
),
1
)
)Excel solution 18 for Cross Product with Repeats, proposed by ferhat CK:
=LET(
r,
TOCOL(
A2:A5&"-"&TOROW(
B2:B4
)
),
n,
REGEXEXTRACT(
A2:A5,
"([0-9])"
),
a,
DROP(
REDUCE(
"",
r,
LAMBDA(
i,
j,
VSTACK(
i,
IFERROR(
DROP(
REDUCE(
"",
--REGEXEXTRACT(
TEXTAFTER(
j,
"-"
),
"([0-9])"
),
LAMBDA(
x,
y,
HSTACK(
x,
IFERROR(
SEQUENCE(
y
)/0,
LEFT(
j,
1
)&LEFT(
TEXTAFTER(
j,
"-"
),
1
)
)
)
)
),
,
1
),
""
)
)
)
),
1
),
b,
BYROW(
WRAPROWS(
a,
6
),
ARRAYTOTEXT
),
c,
MAP(
n,
b,
LAMBDA(
x,
y,
ARRAYTOTEXT(
IFERROR(
SEQUENCE(
x
)/0,
y
)
)
)
),
TRIM(
DROP(
REDUCE(
0,
c,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
,
";"
)
)
)
),
1
)
)
)Excel solution 19 for Cross Product with Repeats, proposed by Jaroslaw Kujawa:
=LET(u;TEXTSPLIT(REDUCE("";A2:A5;LAMBDA(b;y;LET(pkol;REPT(REDUCE("";B2:B4;LAMBDA(a;x;LET(lett;LEFT(x;1);dig;MID(x;3;1);a&";"&REPT(CONCAT(lett)&";";dig))));MID(y;3;1));b&";"&TEXTJOIN(";";1;LEFT(y;1)&TEXTSPLIT(pkol;;";")))));;";");FILTER(u;LEN(u)=2))Excel solution 20 for Cross Product with Repeats, proposed by Ankur Sharma:
=LET(
TB,
TEXTBEFORE,
TA,
TEXTAFTER,
TJ,
TEXTJOIN,
TEXTSPLIT(
TJ(
", ",
,
MAP(
TB(
A2:A5,
"("
),
TB(
TA(
A2:A5,
"("
),
")"
),
LAMBDA(
T,
N,
REPT(
TJ(
", ",
,
MAP(
B2:B4,
LAMBDA(
In,
TJ(
", ",
,
REPT(
T & TB(
In,
"("
) & ", ",
TB(
TA(
In,
"("
),
")"
)
)
)
)
)
) & ", ",
N
)
)
)
),
,
", ",
TRUE
)
)Excel solution 21 for Cross Product with Repeats, proposed by Songglod P.:
=LET(
x,
A2:A5,
y,
B2:B4,
C,
LAMBDA(
a,
UNICHAR(
SEQUENCE(
MID(
a,
3,
1
),
,
UNICODE(
a
),
0
)
)
),
ARR,
LAMBDA(
b,
DROP(
REDUCE(
0,
b,
LAMBDA(
a,
v,
VSTACK(
a,
C(
v
)
)
)
),
1
)
),
TOCOL(
ARR(
x
)&TOROW(
ARR(
y
)
)
)
)Excel solution 22 for Cross Product with Repeats, proposed by Dominic Walsh:
=LET(a,A2:A5,b,B2:B4,
astr,CONCAT(REPT(TEXTBEFORE(a,"(")&"|",TEXTBEFORE(TEXTAFTER(a,"("),")"))),
bstr,CONCAT(REPT(TEXTBEFORE(b,"(")&"|",TEXTBEFORE(TEXTAFTER(b,"("),")"))),
TOCOL(TEXTSPLIT(astr,,"|",1)& TEXTSPLIT(bstr,"|",,1)))Solving the challenge of Cross Product with Repeats with Python
Python solution 1 for Cross Product with Repeats, proposed by Konrad Gryczan, PhD:
import pandas as pd
import itertools
input = pd.read_excel("584 Cross Product.xlsx", usecols="A:B", nrows=5)
test = pd.read_excel("584 Cross Product.xlsx", usecols="D", nrows=49).sort_values(by='Answer Expected').reset_index(drop=True)
combinations = pd.DataFrame(itertools.product(input.iloc[:, 0], input.iloc[:, 1]), columns=['var1', 'var2']).dropna()
combinations['letts'] = combinations['var1'].str.extract(r'([A-Z])') + combinations['var2'].str.extract(r'([A-Z])')
combinations['nums'] = combinations['var1'].str.extract(r'(d+)').astype(int) * combinations['var2'].str.extract(r'(d+)').astype(int)
result = combinations.loc[combinations.index.repeat(combinations['nums'])].sort_values(by='letts').reset_index(drop=True)['letts']
print(result.equals(test['Answer Expected'])) # True
Python solution 2 for Cross Product with Repeats, proposed by Artur Pilipczuk:
import polars as pl
pl.Config.set_tbl_rows(-1)
def multiply_list(structure):
return structure['c1'] * structure['d1']
df=pl.read_excel(r"Excel_Challenge_584 - Cross Product.xlsx",sheet_name="Data")
df=(df.with_columns(pl.all().str.replace_all(")" , ""))
.with_columns(pl.all().str.split("("))
)
col_name="Column1"
col1=(df.select(col_name)
.filter(
~pl.any_horizontal(pl.all().is_null()))
.with_columns(c1=pl.col(col_name).list.get(0).cast(pl.List(pl.Utf8)),
d1=pl.col(col_name).list.get(1).cast(pl.Int16),)
.with_columns(w1=pl.struct("c1", "d1")
.map_elements(multiply_list, return_dtype=pl.List(pl.Utf8)))
.select("w1"))
col_name="Column2"
col2=(df.select(col_name)
.filter(~pl.any_horizontal(pl.all().is_null()))
.with_columns(
c1=pl.col(col_name).list.get(0).cast(pl.List(pl.Utf8)),
d1=pl.col(col_name).list.get(1).cast(pl.Int16),
)
.with_columns(w1=pl.struct("c1", "d1").map_elements(multiply_list, return_dtype=pl.List(pl.Utf8)))
.select("w1")
)
col2=pl.DataFrame({"w2":[col2.to_series().to_list()]})
df_final=col1.join(col2,how="cross")
df_final=(
df_final.explode(pl.col("w1"))
)
for i in range(2):
df_final=df_final.explode(pl.col("w2"))
print(df_final)
Solving the challenge of Cross Product with Repeats with Python in Excel
Python in Excel solution 1 for Cross Product with Repeats, proposed by Alejandro Campos:
import re
df_input = xl("A1:B5", headers=True)
def expand_column(col):
return sum([[m.group(1)] * int(m.group(2)) for v in df_input[col].dropna() if (m := re.match(r"(w+)((d+))", v))], [])
parsed_col1 = expand_column('Column1')
parsed_col2 = expand_column('Column2')
cross_product = [f"{i}{j}" for i in parsed_col1 for j in parsed_col2]
df_result = pd.DataFrame(cross_product, columns=['Result'])
df_result
Python in Excel solution 2 for Cross Product with Repeats, proposed by Ümit Barış Köse, MSc:
import re
from itertools import product, chain
df_input = xl("A1:B5", headers=True)
def expand_column(col):
return list(chain(*map(lambda v: [m.group(1)] * int(m.group(2)) if (m := re.match(r"(w+)((d+))", v)) else [],df_input[col].dropna())))
df_result = pd.DataFrame(
map(lambda p: ''.join(p), product(expand_column('Column1'), expand_column('Column2'))),columns=['My Result'])
Solving the challenge of Cross Product with Repeats with R
R solution 1 for Cross Product with Repeats, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/584 Cross Product.xlsx"
input = read_excel(path, range = "A1:B5")
test = read_excel(path, range = "D1:D49") %>% arrange(`Answer Expected`)
result = expand.grid(input$Column1, input$Column2) %>%
mutate(comb = paste0(str_extract(Var1, "[A-Z]"), str_extract(Var2, "[A-Z]")),
num = as.numeric(str_extract(Var1, "[0-9]")) * coalesce(as.numeric(str_extract(Var2, "[0-9]")),0)) %>%
uncount(num) %>%
arrange(comb) %>%
select(`Answer Expected` = comb)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&