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: 584
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Cross Product with Multiples with Power Query
Power Query solution 1 for Cross Product with Multiples, 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 Multiples, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Accumulate(
Table.ColumnNames(A),
A,
(x, y) =>
Table.TransformColumns(
x,
{
y,
each try
Text.ToList(
Text.Repeat(Text.Select(_, {"A" .. "Z"}), Number.From(Text.Select(_, {"0" .. "9"})))
)
otherwise
null
}
)
),
C = Table.FromColumns(
{
List.TransformMany(
List.Combine(B[Column1]),
each List.Combine(List.RemoveNulls(B[Column2])),
(x, y) => x & y
)
},
{"Answer Expected"}
)
in
CPower Query solution 3 for Cross Product with Multiples, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformColumns(
Source,
{},
each List.Repeat({Text.Start(_ ?? "", 1)}, - Number.From(Text.Middle(_ ?? "", 1)) ?? 0)
),
Return = List.TransformMany(
List.Combine(Transform[Column1]),
(x) => List.Combine(Transform[Column2]),
(x, y) => x & y
)
in
ReturnPower Query solution 4 for Cross Product with Multiples, 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 5 for Cross Product with Multiples, 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
ResultPower Query solution 6 for Cross Product with Multiples, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform,
C = List.Combine,
D = (x) =>
let
x = Text.ToList(x)
in
try List.Repeat({x{0}}, Number.From(x{2})) otherwise {},
E =
let
x = B(Table.ToColumns(A), each C(B(_, D)))
in
B(x{0}, each {_, x{1}}),
F = C(List.Accumulate(E, {}, (s, c) => s & {B(c{1}, each c{0} & _)}))
in
FSolving the challenge of Cross Product with Multiples with Excel
Excel solution 1 for Cross Product with Multiples, 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 2 for Cross Product with Multiples, 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 3 for Cross Product with Multiples, 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 Multiples, 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 Multiples, proposed by Rick Rothstein:
=LET(f,LAMBDA(r,LET(m,-MID(r,2,9),MID(CONCAT(REPT(LEFT(r),m)),SEQUENCE(SUM(m)),1))),TOCOL(f(A2:A5)&TOROW(f(B2:B4))))Excel solution 6 for Cross Product with Multiples, 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 7 for Cross Product with Multiples, 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 8 for Cross Product with Multiples, 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 9 for Cross Product with Multiples, 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 10 for Cross Product with Multiples, proposed by Timothée BLIOT:
=LET(
F,
LAMBDA(
n,
CONCAT(
MAP(
n,
LAMBDA(
x,
LET(
A,
REGEXEXTRACT(
x,
"[A-Z]|d+",
1
),
REPT(
TAKE(
A,
,
1
),
TAKE(
A,
,
-1
)
)
)
)
)
)
),
G,
LAMBDA(
m,
MID(
m,
SEQUENCE(
LEN(
m
)
),
1
)
),
TOCOL(
G(
F(
A2:A5
)
)&TOROW(
G(
F(
B2:B4
)
)
)
)
)
=LET(
F,
LAMBDA(
n,
CONCAT(
MAP(
n,
LAMBDA(
x,
REPT(
LEFT(
x
),
--MID(
x,
3,
1
)
)
)
)
)
),
G,
LAMBDA(
m,
MID(
m,
SEQUENCE(
LEN(
m
)
),
1
)
),
TOCOL(
G(
F(
A2:A5
)
)&TOROW(
G(
F(
B2:B4
)
)
)
)
)Excel solution 11 for Cross Product with Multiples, 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 12 for Cross Product with Multiples, 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 Multiples, 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 14 for Cross Product with Multiples, proposed by Md. Zohurul Islam:
=LET(
a,
B2:B4,
b,
MAP(
a,
LAMBDA(
x,
TEXTBEFORE(
x,
"("
)
)
),
c,
MAP(
a,
LAMBDA(
x,
ABS(
TEXTBEFORE(
TEXTAFTER(
x,
"("
),
")"
)
)
)
),
d,
MAP(
b,
c,
LAMBDA(
x,
y,
REPT(
x&",",
y
)
)
),
e,
CONCAT(
d
),
f,
DROP(
TEXTSPLIT(
e,
","
),
,
-1
),
p,
A2:A5,
q,
MAP(
p,
LAMBDA(
x,
TEXTBEFORE(
x,
"("
)
)
),
r,
MAP(
p,
LAMBDA(
x,
ABS(
TEXTBEFORE(
TEXTAFTER(
x,
"("
),
")"
)
)
)
),
s,
MAP(
q,
r,
LAMBDA(
x,
y,
REPT(
x&",",
y
)
)
),
u,
CONCAT(
s
),
v,
TOCOL(
DROP(
TEXTSPLIT(
u,
","
),
,
-1
)
),
result,
TOCOL(
v&f
),
result
)Excel solution 15 for Cross Product with Multiples, 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 16 for Cross Product with Multiples, 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 17 for Cross Product with Multiples, 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 18 for Cross Product with Multiples, 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 19 for Cross Product with Multiples, 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 20 for Cross Product with Multiples, proposed by Eddy Wijaya:
=LET(
f,
LAMBDA(
arr,
DROP(
REDUCE(
0,
DROP(
REDUCE(
0,
arr,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
sp,
TEXTSPLIT(
v,
{"(",
")"}
),
REPT(
@sp,
CHOOSECOLS(
sp,
2
)
)
)
)
)
),
1
),
LAMBDA(
a,
v,
VSTACK(
a,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)
)
)
),
1
)
),
c_a,
f(
A2:A5
),
c_b,
f(
B2:B4
),
DROP(
REDUCE(
0,
c_a,
LAMBDA(
a,
v,
VSTACK(
a,
v&c_b
)
)
),
1
)
)Excel solution 21 for Cross Product with Multiples, 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 Multiples, 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 Multiples with Python
Python solution 1 for Cross Product with Multiples, 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 Multiples, proposed by Abdallah Ally:
import pandas as pd
import re
# Create a function to create cross product
def expand_values(text):
if pd.isna(text):
return []
before = re.findall('[A-Z]+', text)[0]
after = int(re.findall('d+', text)[0])
return [before] * after
file_path = 'Excel_Challenge_584 - Cross Product.xlsx'
df = pd.read_excel(file_path, usecols='A:B', nrows=4)
# Perform data manipulation
df = df.map(expand_values)
col1 = [x for y in df.Column1 for x in y if y]
col2 = [x for y in df.Column2 for x in y if y]
result = [y + x for y in col1 for x in col2]
df = pd.DataFrame({'My Answer': result})
df
Python solution 3 for Cross Product with Multiples, 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 Multiples with Python in Excel
Python in Excel solution 1 for Cross Product with Multiples, 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 Multiples with R
R solution 1 for Cross Product with Multiples, 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
&&