Concat column B numbers into column 1 alphabets. To align in Expected answer column, all first numbers will come first from all rows (A1, B2, C3, D91, E12), then all second numbers (A4, B33, C4, D92) and so on.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 506
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Interleave Numbers into Letters with Power Query
_x000D_Power Query solution 1 for Interleave Numbers into Letters, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"A",
each [
a = [Alphabets],
b = List.Transform(
List.Combine(
List.Transform(
Text.Split(Text.Replace([Numbers], "-", ".."), ", "),
each Expression.Evaluate("{" & _ & "}")
)
),
each a & Text.From(_)
)
][b]
),
Tab = Table.FromColumns({List.RemoveNulls(List.Combine(List.Zip(Ans[A])))}, {"Answer Expected"})
in
TabPower Query solution 2 for Interleave Numbers into Letters, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc = Table.AddColumn(
Source,
"Answer",
each
let
a = [Alphabets],
b = Text.SplitAny([Numbers], ", "),
c = List.Combine(
List.Transform(
List.Select(b, each _ <> ""),
each
if Text.Contains(_, "-") then
let
d = Text.Split(_, "-"),
e = {Number.From(d{0}) .. Number.From(d{1})}
in
e
else
{_}
)
),
f = List.Transform(c, each a & Text.From(_))
in
f
),
Sol = List.Combine(List.Transform(List.Zip(Calc[Answer]), List.RemoveNulls))
in
SolPower Query solution 3 for Interleave Numbers into Letters, proposed by Szabolcs Phraner:
let
Source...,
//Format string Values in Numbers Column as list Expressions, then transform into actual Lists, concatenate each List Number with Related Alphabet Value
ValuesToLists = Table.TransformRows( Source,
each
[
Row = _,
NumbersToList = Expression.Evaluate( "{ " & Text.Replace([Numbers],"-","..") & " }" ),
Concatenate = List.Transform(NumbersToList, each Row[Alphabets] & Text.From(_)
)
] [Concatenate]
),
//Get a Lists of Maximum Iterations to Conctonate List values in the Right Order
IterationList = List.Numbers(0,
List.Max( List.Transform( ValuesToLists, List.Count ) )
),
ConcatonateLists = List.Accumulate( IterationList
,
{},
(L, I) =>
L &
List.Accumulate(
ValuesToLists,
{},
(s,c) => if List.Count(c) -1 < I then s else s & {c{I}}
)
)
in
ConcatonateLists
Solving the challenge of Interleave Numbers into Letters with Excel
_x000D_Excel solution 1 for Interleave Numbers into Letters, proposed by Bo Rydobon 🇹🇭:
=TOCOL(A2:A6&DROP(REDUCE(0,B2:B6,LAMBDA(a,v,VSTACK(a,REDUCE(0,TEXTSPLIT(v,","),LAMBDA(c,r,HSTACK(c,LET(n,TEXTSPLIT(r,"-"),SEQUENCE(,MAX(n-@n+1),@n)))))))),1,1),3,1)Excel solution 2 for Interleave Numbers into Letters, proposed by John V.:
=TOCOL(DROP(REDUCE(0,B2:B6,LAMBDA(c,y,HSTACK(c,@+A6:y&REDUCE(0,TEXTSPLIT(y,", "),LAMBDA(a,v,VSTACK(a,IFERROR(ROW(INDIRECT(SUBSTITUTE(v,"-",":"))),v))))))),1),2)Excel solution 3 for Interleave Numbers into Letters, proposed by محمد حلمي:
=TEXTSPLIT(CONCAT(MAP(A2:A6,B2:B6,LAMBDA(e,v,
CONCAT(BYROW(TEXTSPLIT(v,"-",",",,,0),
LAMBDA(a,CONCAT(e&SEQUENCE(
MAX(a-@a)+1,,@a)&-1)))&-1)))&-1),,-1,1)Excel solution 4 for Interleave Numbers into Letters, proposed by Kris Jaganah:
=TOCOL(TEXTSPLIT(TEXTJOIN(".",,MAP(A2:A6,B2:B6,LAMBDA(v,w,LET(a,TEXTSPLIT(w,"-",", "),ARRAYTOTEXT(v&TEXTSPLIT(ARRAYTOTEXT(MAP(TAKE(a,,1),IFNA(TAKE(a,,-1),TAKE(a,,1)),LAMBDA(x,y,ARRAYTOTEXT(SEQUENCE(y-x+1,,x))))),", ")))))),", ","."),3,1)Excel solution 5 for Interleave Numbers into Letters, proposed by Julian Poeltl:
=TOCOL(
TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
B2:B6,
A2:A6,
LAMBDA(
N,
Al,
LET(
SP,
TEXTSPLIT(
N,
", "
),
TEXTJOIN(
",",
,
IF(
ISNUMBER(
SEARCH(
"-",
SP
)
),
MAP(
SP,
LAMBDA(
A,
TEXTJOIN(
",",
,
Al&SEQUENCE(
TEXTAFTER(
A,
"-"
)-TEXTBEFORE(
A,
"-"
)+1,
,
TEXTBEFORE(
A,
"-"
)
)
)
)
),
Al&SP
)
)
)
)
)
),
",",
"|"
),
3,
1
)Excel solution 6 for Interleave Numbers into Letters, proposed by Timothée BLIOT:
=TOCOL(TEXTSPLIT(TEXTJOIN("/",,MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(A,WRAPROWS(TRANSPOSE(--REGEXEXTRACT(y,"d+(?=-)|(?<=-)d+",1)),2), B,IFNA(REDUCE(0,SEQUENCE(ROWS(A)),LAMBDA(w,v, HSTACK(w, IFNA( SEQUENCE(,@(TAKE(INDEX(A,v,),,-1)-@TAKE(INDEX(A,v,),v,1)+1), @TAKE(INDEX(A,v,),v,1)),0)))),0),C,SORT(HSTACK(B,IFNA(--(REGEXEXTRACT( y,"(?0)))))),"|","/"),3,1)Excel solution 7 for Interleave Numbers into Letters, proposed by Sunny Baggu:
=LET(
r, A2:B6,
e, DROP(
REDUCE(
"🌼",
SEQUENCE(ROWS(r)),
LAMBDA(x, y,
VSTACK(
x,
LET(
_c1, INDEX(r, y, 1),
_c2, INDEX(r, y, 2),
IFNA(
HSTACK(
_c1,
LET(
_ts, TEXTSPLIT(_c2, "-", ",", , , 0) + 0,
d, IF(TAKE(_ts, , -1), MMULT(_ts, {-1; 1}), 0),
v, DROP(
REDUCE("", d, LAMBDA(a, v, VSTACK(a, IFERROR(SEQUENCE(, v + 1, 0), 0)))),
1
),
TOROW(TAKE(_ts, , 1) + v, 3)
)
),
_c1
)
)
)
)
),
1
),
TOCOL(IF(DROP(e, , 1), TAKE(e, , 1) & DROP(e, , 1), ""), 3, 1)
)Excel solution 8 for Interleave Numbers into Letters, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,IFNA(DROP(REDUCE("",B2:B6,LAMBDA(i,j,VSTACK(i,REDUCE("",TEXTSPLIT(j,", "),LAMBDA(a,b,LET(c,TEXTBEFORE(b,"-"),HSTACK(a,IFNA(SEQUENCE(,TEXTAFTER(b,"-")-c+1,--c),--b)))))))),1,1),""),TOCOL(TRANSPOSE(IF(n,A2:A6&n)),3))Excel solution 9 for Interleave Numbers into Letters, proposed by Bilal Mahmoud kh.:
=TEXTSPLIT(TEXTJOIN("-",,BYCOL(IFERROR(TRIM(TEXTSPLIT(TEXTJOIN("|",,MAP(B2:B6,LAMBDA(n,TEXTJOIN(",",,SCAN("",TRIM(TEXTSPLIT(n,",")),LAMBDA(x,y,IF(IFERROR(FIND("-",y,1),0)<1,y,TEXTJOIN(",",,SEQUENCE(,--TEXTAFTER(y,"-")-(--TEXTBEFORE(y,"-"))+1,--TEXTBEFORE(y,"-")))))))))),{"-",","},"|")),""),LAMBDA(x,TEXTJOIN("-",,(FILTER(A2:A6,x<>"")&FILTER(x,x<>"")))))),,"-")Excel solution 10 for Interleave Numbers into Letters, proposed by JvdV -:
=TOCOL(A2:A6&DROP(REDUCE(0,REGEXREPLACE(B2:B6,"(d+)(-(d+))?","R$1C1:R${2:+$3:$1}C1"),LAMBDA(a,b,VSTACK(a,TOROW(REDUCE(0,TEXTSPLIT(b,", "),LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(y,0))))))))),1,1),2,1)Excel solution 11 for Interleave Numbers into Letters, proposed by Eddy Wijaya:
=LET(genNewDB,
DROP(
REDUCE(
0,
B2:B6,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
raw,
v,
d,
TEXTSPLIT(
raw,
","
),
col,
TOCOL(
d,
0
),
catHyphen,
HSTACK(
col,
IF(
ISNUMBER(
SEARCH(
"-",
col
)
),
"Range",
""
)
),
nonHyphen,
VALUE(
TAKE(
FILTER(
catHyphen,
TAKE(
catHyphen,
,
-1
)=""
),
,
1
)
),
filterHyphen,
FILTER(
catHyphen,
TAKE(
catHyphen,
,
-1
)="Range"
),
getSequence,
DROP(
REDUCE(
0,
TAKE(
filterHyphen,
,
1
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_split,
VALUE(
TEXTSPLIT(
v,
"-"
)
),
SEQUENCE(
MAX(
_split
)-MIN(
_split
)+1,
,
MIN(
_split
)
)
)
)
)
),
1
),
checker,
IF(
--ISERR(
getSequence
)>=1,
nonHyphen,
getSequence
),
fixList,
UNIQUE(
VSTACK(
nonHyphen,
checker
)
),
HSTACK(
IF(
{1,
0},
OFFSET(
raw,
,
-1
),
SORT(
fixList,
1,
1
)
),
SEQUENCE(
ROWS(
fixList
)
)
)
)
)
)
),
1
),
indexing,
UNIQUE(
TAKE(
genNewDB,
,
1
)
),
fixDb,
HSTACK(
genNewDB,
MATCH(
TAKE(
genNewDB,
,
1
),
indexing,
0
)
),
genSorting,
(CHOOSECOLS(
fixDb,
3
)-1)*COUNTA(
indexing
)+CHOOSECOLS(
fixDb,
4
),
sortedDB,
SORT(
HSTACK(
fixDb,
genSorting
),
5,
1
),
CHOOSECOLS(
HSTACK(
TAKE(
sortedDB,
,
2
),
CHOOSECOLS(
sortedDB,
1
)&CHOOSECOLS(
sortedDB,
2
)
),
3
))Excel solution 12 for Interleave Numbers into Letters, proposed by Edwin Tisnado:
=TOCOL(
DROP(
REDUCE(
0,
SEQUENCE(
7
),
LAMBDA(
x,
y,
VSTACK(
x,
CHOOSECOLS(
A2:A6&DROP(
REDUCE(
0,
B2:B6,
LAMBDA(
r,
c,
VSTACK(
r,
REDUCE(
0,
TEXTSPLIT(
c,
", "
& ),
LAMBDA(
x,
y,
LET(
a,
TEXTAFTER(
y,
"-",
,
,
,
y
),
b,
TEXTBEFORE(
y,
"-",
,
,
,
y
),
HSTACK(
x,
IF(
a-b,
SEQUENCE(
,
a-b+1,
b
),
--a
)
)
)
)
)
)
)
),
1,
1
),
y
)
)
)
),
1
),
2
)Excel solution 13 for Interleave Numbers into Letters, proposed by El Badlis Mohd Marzudin:
=LET(f,LAMBDA(x,TEXTBEFORE(x,"-")),
a,IFERROR(DROP(REDUCE("",B2:B6,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,", ")))),1),""),
b,MAP(a,LAMBDA(x, ARRAYTOTEXT(IF(ISNUMBER(SEARCH("-",x)),SEQUENCE(TEXTAFTER(x,"-")-f(x)+1,,f(x)),x)))),
TOCOL(A2:A6&TEXTSPLIT(TEXTJOIN("|",,BYROW(b,LAMBDA(x,TEXTJOIN(", ",1,x)))),", ","|"),3,1))Excel solution 14 for Interleave Numbers into Letters, proposed by JORGE MANUEL MOSTACERO ASENCIO:
=ENCOL(
A2:A6&ESPACIOS(
DIVIDIRTEXTO(
MATRIZATEXTO(
BYROW(
SI.ERROR(
MAP(
DIVIDIRTEXTO(
MATRIZATEXTO(
B2:B6
);
",";
";";
1;
;
""
);
LAMBDA(
X;
CONCAT(
SI(
ESNUMERO(
X+0
);
X;
UNIRCADENAS(
",";
;
SECUENCIA(
;
TEXTODESPUES(
X;
"-"
)-TEXTOANTES(
X;
"-"
)+1;
TEXTOANTES(
X;
"-"
)
)
)
)
)
)
);
""
);
LAMBDA(
X;
UNIRCADENAS(
",";
;
X
)
)
)
);
",";
";";
1;
;
NOD()
)
);
2;
VERDADERO
)Solving the challenge of Interleave Numbers into Letters with Python
_x000D_Python solution 1 for Interleave Numbers into Letters, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "506 Align Concated Alphabets & Numbers.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=5)
test = pd.read_excel(path, usecols="C", nrows=21)
def replace_range(input_str):
replaced_parts = []
for part in re.split(", ", input_str):
if "-" in part:
start, end = map(int, part.split("-"))
replaced_parts.extend(range(start, end + 1))
else:
replaced_parts.append(int(part))
return ", ".join(map(str, replaced_parts))
df = pd.DataFrame(input)
df['Numbers'] = df['Numbers'].apply(replace_range).str.split(", ")
df = df.explode('Numbers').reset_index(drop=True)
df["Answer Expected"] = df["Alphabets"] + df["Numbers"].astype(str)
df = df.groupby("Alphabets")["Answer Expected"].apply(list).reset_index()
df1 = df["Answer Expected"].tolist()
df1 = [lst + [""] * (max(map(len, df1)) - len(lst)) for lst in df1]
df1 = [item for sublist in zip(*df1) for item in sublist if item]
print(all(df1 == test["Expected Answer"].values)) # True
Solving the challenge of Interleave Numbers into Letters with Python in Excel
_x000D_Python in Excel solution 1 for Interleave Numbers into Letters, proposed by Alejandro Campos:
df = xl("A1:B6", headers=True)
def expand_numbers(number_str):
numbers = []
parts = number_str.split(', ')
for part in parts:
if '-' in part:
start, end = map(int, part.split('-'))
numbers.extend(range(start, end + 1))
else:
numbers.append(int(part))
return numbers
df['Expanded_Numbers'] = df['Numbers'].apply(expand_numbers)
results = []
max_length = max(df['Expanded_Numbers'].apply(len))
for i in range(max_length):
for index, row in df.iterrows():
if i < len(row['Expanded_Numbers']):
results.append(f"{row['Alphabets']}{row['Expanded_Numbers'][i]}")
result_df = pd.DataFrame(results, columns=['Expected Answer'])
result_df
Python in Excel solution 2 for Interleave Numbers into Letters, proposed by Abdallah Ally:
# Create a function to split and combine text
def split_concat(text1, text2):
text = text2.split(', ')
str_nums = [x.split('-') for x in text]
str_nums = [x if len(x) == 1 else
[str(y) for y in range(int(x[0]), int(x[1]) + 1)]
for x in str_nums
]
str_nums = [text1 + x for y in str_nums for x in y]
return ', '.join(str_nums)
df = xl("A1:B6", headers=True)
# Perform data munging
df['NewNums'] = df.apply(
lambda x: split_concat(x['Alphabets'], x['Numbers']), axis=1
)
df = df['NewNums'].str.split(', ', expand=True)
values = [x for x in df.values.flatten(order='F') if x]
values
Python in Excel solution 3 for Interleave Numbers into Letters, proposed by Anshu Bantra:
def expand_list(lst):
expanded_list=[]
for item in lst.split(', '):
if '-' in item:
start, end = map(int, item.split('-'))
expanded_list.extend(range(start, end + 1))
else:
expanded_list.append(int(item))
return expanded_list
df = xl("A1:B6", headers=True)
df['Numbers'] = df['Numbers'].apply(expand_list)
df1 = df.explode('Numbers',ignore_index=True )
df1['Position'] = df1.groupby('Alphabets').cumcount() + 1
df1.sort_values(by=['Position','Alphabets'], inplace=True)
df1['Ans'] = df1['Alphabets']+df1['Numbers'].astype(str)
df1['Ans'].values
Solving the challenge of Interleave Numbers into Letters with R
_x000D_R solution 1 for Interleave Numbers into Letters, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/506 Align Concated Alphabets & Numbers.xlsx"
input = read_excel(path, range = "A1:B6")
test = read_excel(path, range = "C1:C22")
replace_range <- function(input) {
input %>%
str_split(", ") %>%
unlist() %>%
map_chr(~ if (str_detect(.x, "-")) {
range <- str_split(.x, "-")[[1]] %>%
as.numeric()
paste(seq(range[1], range[2]), collapse = ", ")
} else {
.x
}) %>%
paste(collapse = ", ")
}
result = input %>%
mutate(Numbers = map_chr(Numbers, replace_range) %>% str_split(., ", ")) %>%
unnest_wider(Numbers, names_sep = "_") %>%
pivot_longer(cols = starts_with("Numbers"), values_to = "Value", names_to = NULL, cols_vary = "slowest") %>%
filter(!is.na(Value)) %>%
unite("Expected Answer", c("Alphabets", "Value"), sep = "") %>%
select("Expected Answer")
identical(result$`Expected Answer`, test$`Expected Answer`)
# [1] TRUE
R solution 2 for Interleave Numbers into Letters, proposed by Konrad Gryczan, PhD:
Tried to mimic zip function from Python.
library(tidyverse)
library(readxl)
path = "Excel/506 Align Concated Alphabets & Numbers.xlsx"
input = read_excel(path, range = "A1:B6")
test = read_excel(path, range = "C1:C22") %>% pull()
replace_range <- function(input) {
input %>%
str_split(", ") %>%
unlist() %>%
map_chr(~ if (str_detect(.x, "-")) {
range <- str_split(.x, "-")[[1]] %>%
as.numeric()
paste(seq(range[1], range[2]), collapse = ", ")
} else {
.x
}) %>%
paste(collapse = ", ")
}
zip_and_flatten <- function(list_of_lists) {
max_length <- max(map_int(list_of_lists, length))
filled_lists <- map(list_of_lists, ~ c(.x, rep(NA, max_length - length(.x))))
flattened <- filled_lists %>%
transpose() %>%
map(unlist) %>%
unlist() %>%
discard(is.na)
return(flattened)
}
result = input %>%
mutate(Numbers = map_chr(Numbers, replace_range) %>% str_split(., ", "),
Numbers = map2(Alphabets, Numbers, ~ paste(.x, .y, sep = "")))
flattened_list <- zip_and_flatten(result$Numbers)
identical(flattened_list, test)
# [1] TRUE
Solving the challenge of Interleave Numbers into Letters with Excel VBA
_x000D_Excel VBA solution 1 for Interleave Numbers into Letters, proposed by Ümit Barış Köse, MSc:
Sub C506()
Dim table(1 To 5, 1 To 10) As Integer
Dim numbers() As String
Dim rng() As String
Dim x1 As Integer, x2 As Integer, max1 As Integer
Dim s2 As String
max1 = 0
For i1 = 2 To 6
s1 = Trim(Cells(i1, 2).Value)
numbers = Split(s1, ";")
j2 = 1
For j1 = LBound(numbers) To UBound(numbers)
s2 = numbers(j1)
If InStr(s2, "-") > 0 Then
rng = Split(s2, "-")
x1 = CInt(Trim(rng(0)))
x2 = CInt(Trim(rng(1)))
For k = x1 To x2
table(i1 - 1, j2) = k
j2 = j2 + 1
If k > 0 Then table(i1 - 1, 10) = table(i1 - 1, 10) + 1
Next k
Else
table(i1 - 1, j2) = CInt(s2)
j2 = j2 + 1
If CInt(s2) > 0 Then table(i1 - 1, 10) = table(i1 - 1, 10) + 1
End If
Next j1
If max1 < table(i1 - 1, 10) Then max1 = table(i1 - 1, 10)
Next i1
Row1 = 2
For i3 = 1 To max1
For i4 = 2 To 6
If table(i4 - 1, i3) > 0 Then
Cells(Row1, 4) = Cells(i4, 1) & table(i4 - 1, i3)
Row1 = Row1 + 1
End If
Next i4
Next i3
End Sub