Generate the number outlining. If single X – 1, 2, 3… If double X – 1.1, 1.2… If triple x – 1.1.1. 1.1.2…
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 416
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Number Outline Sequence with Power Query
Power Query solution 1 for Generate Number Outline Sequence, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
List = Source[Strings],
Output = List.Generate(
() => [a = 0, b = List{a}, d = {1}, f = "1"],
each [a] < List.Count(List),
each [
a = [a] + 1,
b = List{a},
c = Text.Length(b),
d = if c > Text.Length([b]) then [d] & {1} else List.FirstN([d], c - 1) & {[d]{c - 1} + 1},
e = List.Transform(d, Text.From),
f = Text.Combine(e, ".")
],
each [Strings = [b], Answer = [f]]
),
Return = Table.FromRecords(Output)
in
ReturnPower Query solution 2 for Generate Number Outline Sequence, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Strings", type text}}),
#"Grouped Rows" =
let
t = Table.Group(
#"Changed Type",
{"Strings"},
{{"a", each _, type table}},
GroupKind.Local,
(x, y) => Int64.From(y[Strings] = x[Strings])
)[a],
tc = List.Count(t)
in
List.Transform(
{0 .. tc - 1},
(r) =>
let
tbl = Table.Buffer(t{r}),
rc = Table.RowCount(tbl),
s = List.Buffer(tbl[Strings]),
m = List.Max(List.Transform(s, (a) => Text.Length(a)))
in
Table.FromColumns(
{
s,
List.Generate(
() => [
t = tbl{i}[Strings],
i = 0,
tl = Text.Length(t),
id = List.Buffer(List.Zip({{r + 1} & List.Repeat({0}, m - 1), {1 .. m}}))
],
each [i] < rc,
each [
t = tbl{i}[Strings],
i = [i] + 1,
tl = Text.Length(t),
id = List.Buffer(
List.Transform(
[id],
(a) =>
if a{1} = tl then
{a{0} + 1, a{1}}
else if a{1} > tl then
{a{0} * 0, a{1}}
else
a
)
)
],
each Text.Combine(List.Transform([id], (c) => Text.From(c{0})), ".")
)
},
{"String", "String ID"}
)
),
Custom1 = Table.Combine(#"Grouped Rows")
in
Custom1Solving the challenge of Generate Number Outline Sequence with Excel
Excel solution 1 for Generate Number Outline Sequence, proposed by Bo Rydobon 🇹🇭:
=MID(
DROP(
REDUCE(
0,
A2:A20,
LAMBDA(
a,
v,
LET(
r,
A2:v,
p,
XLOOKUP(
v,
DROP(
r,
-1
),
DROP(
a,
1
),
,
,
-1
),
VSTACK(
a,
IF(
v>@TAKE(
VSTACK(
0,
r
),
-2
),
TAKE(
a,
-1
)&".1",
TEXTBEFORE(
p,
".",
-1
)&"."&TEXTAFTER(
p,
".",
-1
)+1
)
)
)
)
),
1
),
3,
9
)Excel solution 2 for Generate Number Outline Sequence, proposed by John V.:
=SCAN(
0,
A2:A20,
LAMBDA(
a,
v,
LET(
b,
LEN(
v
),
IF(
b>LEN(
OFFSET(
v,
-1,
)
),
a&".1",
LEFT(
a,
2*b-2
)&1+MID(
a,
2*b-1,
1
)
)
)
)
)Excel solution 3 for Generate Number Outline Sequence, proposed by محمد حلمي:
=SCAN(
0,
A2:A20,
LAMBDA(
a,
v,
LET(
i,
LEN(
v
),
x,
LEN(
@+TAKE(
A1:v,
-2
)
),
Y,
LAMBDA(
W,
TEXTBEFORE(
W,
".",
-1
) &"."&RIGHT(
W
)+1
),
IFS(
i=1,
LEFT(
a
)+1,
i>x,
a&".1",
i=x,
Y(
a
),
1,
Y(
LEFT(
a,
i*2-1
)
)
)
)
)
)Excel solution 4 for Generate Number Outline Sequence, proposed by Kris Jaganah:
=BYROW(TRANSPOSE(
SUBSTITUTE(
TEXTSPLIT(
ARRAYTOTEXT(
BYCOL(
SEQUENCE(
,
9
),
LAMBDA(
v,
TEXTJOIN(
"#",
,
SCAN(
0,
LEN(
A2:A20
),
LAMBDA(
x,
y,
IFS(
y=v,
x+1,
y>v,
x,
1,
0
)
)
)
)
)
)
),
"#",
", "
),
"0",
""
)
),
LAMBDA(
w,
TEXTJOIN(
".",
,
w
)
)Excel solution 5 for Generate Number Outline Sequence, proposed by Timothée BLIOT:
=LET(A,
MAP(SEQUENCE(
19
),
LAMBDA(x,
SUM(--(TAKE(
A2:A20,
x
)="X")))),
B,
VSTACK(
0,
SCAN(
0,
SEQUENCE(
18
),
LAMBDA(
w,
v,
IF(
INDEX(
A,
v
)=INDEX(
A,
v+1
),
IF(
INDEX(
A2:A20,
v+1
)="XX",
w+1,
w
),
0
)
)
)
),
C,
SCAN(
0,
A2:A20,
LAMBDA(
w,
v,
IF(
v="XXX",
w+1,
0
)
)
),
D,
HSTACK(
B,
C
),
BYROW(
HSTACK(
A,
IF(
D=0,
"",
"."&D
)
),
LAMBDA(
x,
CONCAT(
x
)
)
))Excel solution 6 for Generate Number Outline Sequence, proposed by JvdV –:
=TEXT(
SCAN(
0,
A2:A20,
LAMBDA(
a,
b,
LEFT(
a&0,
LEN(
b
)
)+1
)
),
0&REPT(
".0",
LEN(
A2:A20
)-1
)
)Excel solution 7 for Generate Number Outline Sequence, proposed by Pieter de Bruijn:
=MAP(
A2:A20,
LAMBDA(
x,
SUM(
N(
A2:x="X"
)
)&IF(
LEN(
x
)-1,
CONCAT(
"."&MAP(
REPT(
"X",
SEQUENCE(
LEN(
x
)-1
)
),
LAMBDA(
y,
SUM(
N(
XLOOKUP(
y,
x:A2,
x:A2,
,
,
-1
):x=y&"X"
)
)
)
)
),
""
)
)
)Excel solution 8 for Generate Number Outline Sequence, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(k,
REDUCE({0,
0,
0},
LEN(
A2:A20
),
LAMBDA(a,
p,
LET(f,
MAKEARRAY(
2,
3,
LAMBDA(
i,
j,
IF(
AND(
i=1
),
IF(
j=p,
1,
0
),
IF(
j<=p,
1,
0
)
)
)
),
VSTACK(a,
TAKE(
f,
-1
)*(TAKE(
f,
1
)+TAKE(
a,
-1
)))))),
BYROW(
DROP(
k,
1
),
LAMBDA(
l,
TEXTJOIN(
".",
1,
IF(
l=0,
"",
l
)
)
)
))Excel solution 9 for Generate Number Outline Sequence, proposed by Tyler Cameron:
=LET(
a,
SCAN(
0,
A2:A20,
LAMBDA(
x,
y,
IF(
y="X",
x+1,
x
)
)
),
b,
SCAN(
0,
A2:A20,
LAMBDA(
x,
y,
IF(
y="XX",
x+1,
IF(
y="X",
x-x,
x
)
)
)
),
c,
SCAN(
0,
A2:A20,
LAMBDA(
x,
y,
IF(
y="XXX",
x+1,
0
)
)
),
d,
IF(
b=0,
"",
b
),
e,
IF(
c=0,
"",
c
),
MAKEARRAY(
COUNTA(
A2:A20
),
1,
LAMBDA(
r,
c,
TEXTJOIN(
".",
TRUE,
INDEX(
a,
r
),
INDEX(
d,
r
),
INDEX(
e,
r
)
)
)
)
)Excel solution 10 for Generate Number Outline Sequence, proposed by Alexandra Popoff:
=> XXX). I added some commentaries in the jpg.
Fx_Seq_Dyna = Lambda(
z_Input,
LET(
z_len,
LEN(
z_Input
),
z_Max_Y,
ROWS(
z_Input
),
z_Max_X,
MAX(
z_len
),
z_seq_Y,
SEQUENCE(
z_Max_Y,
1,
1,
1
),
z_Input_Arr,
MAKEARRAY(
z_Max_Y,
z_Max_X,
LAMBDA(
z_Y,
z_X,
IF(
z_X <= INDEX(
z_len,
z_Y,
1
),
1,
0
)
)
),
z_Delta,
MAKEARRAY(
ROWS(
z_Input
),
MAX(
z_len
),
LAMBDA(
z_y,
z_x,
N(
INDEX(
z_len,
z_y
) = z_x
)
)
),
z_Adj,
z_Input_Arr - VSTACK(
SEQUENCE(
1,
z_Max_X,
0,
0
),
DROP(
z_Input_Arr,
-1
)
),
z_Delta_Adj,
MAKEARRAY(
z_Max_Y,
z_Max_X,
LAMBDA(
z_Y,
z_X,
MIN(
1,
INDEX(
z_Delta,
z_Y,
z_X
) + MAX(
0,
INDEX(
z_Adj,
z_Y,
z_X
)
)
)
)
),
z_Arr,
MAKEARRAY(
z_Max_Y,
z_Max_X,
LAMBDA(
z_Y,
z_X,
IF(
z_X = 1,
SUM(
TAKE(
z_Delta_Adj,
z_Y,
1
),
0
),
LET(
Test_Arr,
BYROW(
TAKE(
z_Delta_Adj,
z_Y,
z_X - 1
),
LAMBDA(
z_i,
SUM(
& z_i
)
)
),
IFERROR(
SUM(
DROP(
TAKE(
INDEX(
z_Delta_Adj,
,
z_X
),
z_Y
),
MAX(
0,
XLOOKUP(
1,
Test_Arr,
TAKE(
z_seq_Y,
z_Y
),
0,
0,
-1
) - 1
)
)
),
0
)
)
)
)
),
BYROW(
SUBSTITUTE(
z_Arr,
0,
""
),
LAMBDA(
z_i,
TEXTJOIN(
".",
TRUE,
z_i
)
)
)
)
)Solving the challenge of Generate Number Outline Sequence with Python
Python solution 1 for Generate Number Outline Sequence, proposed by Cristobal Salcedo Beltran:
Code
=================
import pandas as pd
excel_file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_416 - Outline Numbering.xlsx"
pandas_df = pd.read_excel(excel_file_path, usecols=[0], header=0)
max_depth = pandas_df['Strings'].apply(len).max()
counters = [0] * max_depth
def get_number(s):
global counters
level = len(s)
counters[level-1] += 1
counters[level:] = [0] * (max_depth - level)
return '.'.join(str(counters[i]) for i in range(level) if counters[i] > 0)
pandas_df['HierarchicalNumber'] = pandas_df['Strings'].apply(get_number)
print(pandas_df)
Solving the challenge of Generate Number Outline Sequence with R
R solution 1 for Generate Number Outline Sequence, proposed by Konrad Gryczan, PhD:
Easier that it looks. Done in R.
library(tidyverse)
library(readxl)
input = read_excel("Excel/416 Outline Numbering.xlsx", range = "A1:A20")
test = read_excel("Excel/416 Outline Numbering.xlsx", range = "B1:B20")
result = input %>%
mutate(level = str_count(Strings, "X")) %>%
mutate(first_lev = cumsum(level == 1)) %>%
mutate(second_level = cumsum(level == 2), .by = first_lev) %>%
mutate(third_level = cumsum(level == 3), .by = c(first_lev, second_level)) %>%
mutate(`Answer Expected` = case_when(
level == 1 ~ paste0(first_lev),
level == 2 ~ paste0(first_lev, ".", second_level),
level == 3 ~ paste0(first_lev, ".", second_level, ".", third_level)
)) %>%
select(`Answer Expected`)
&&