Given are Day of week names in 3 languages. For each language, extract minimum characters from left to make the abbreviations unique. For ex – In English week names, you would need to extract minimum 2 characters from left to make abbreviations unique. If you extract only 1 character, then Sunday and Saturday will be S and Tuesday and Thursday will be T. Hence, these abbreviations will not be unique. If you extract 2 characters then Su, Mo, Tu, We, Th, Fr and Sa which are unique.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 638
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Unique Day Abbreviations with Power Query
Power Query solution 1 for Generate Unique Day Abbreviations, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
H = List.Transform,
F = (r) =>
[
B = H(
r,
(t) =>
let
a = Text.Length(t),
b = H({1 .. a}, (x) => Text.RemoveRange(t, x, a - x))
in
b
),
C = List.Zip(B),
D = List.Zip(H({0 .. 6}, (z) => H(List.Zip(C){z}, each if _ = null then r{z} else _))),
E = List.Select(D, each List.Distinct(_) = _){0}
][E],
G = Table.FromColumns(H(Table.ToColumns(A), each F(_)), Table.ColumnNames(A))
in
GPower Query solution 2 for Generate Unique Day Abbreviations, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Col = Table.ToColumns(Source),
LG = List.Transform(
Col,
(n) =>
List.Last(
List.Generate(
() => [x = 1, y = 0, w = 0],
each [w] < List.Count(n),
each [
x = [x] + 1,
y = List.Count(List.Distinct(z)),
z = List.Transform(n, (k) => try Text.Range(k, 0, [x]) otherwise k),
w = [y]
],
each [z]
)
)
),
Sol = Table.FromColumns(LG, Table.ColumnNames(Source))
in
SolPower Query solution 3 for Generate Unique Day Abbreviations, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
unique = (texts) =>
List.RemoveNulls(
List.Generate(
() => [n = 1, l = List.Transform(texts, (x) => Text.Start(x, n))],
each [n] <= List.Max(List.Transform(texts, Text.Length)),
each [n = [n] + 1, l = List.Transform(texts, (x) => Text.Start(x, n))],
each if List.IsDistinct([l]) then [l] else null
)
){0},
ColNames = Table.ColumnNames(Source),
Result = Table.FromColumns(List.Transform(Table.ToColumns(Source), unique), ColNames)
in
ResultPower Query solution 4 for Generate Unique Day Abbreviations, proposed by Md. Zohurul Islam:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumns(Source, {{"English", each Text.Start(_, 2), type text}}),
b = Table.TransformColumns(a, {{"Catalan", each Text.Start(_, 4), type text}}),
c = Table.TransformColumns(b, {{"Hebrew", each Text.Start(_, 7), type text}})
in
cPower Query solution 5 for Generate Unique Day Abbreviations, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToColumns(S),
Fx = (x) =>
let
A = x,
Fy = (y) =>
let
B = y,
C = List.Generate(
() => [i = 0],
each [i] < Text.Length(B),
each [i = [i] + 1],
each Text.Range(B, 0, [i] + 1)
)
in
C,
b = List.Transform(A, each Fy(_)),
c = List.Max(List.Transform(b, each List.Count(_))),
d = List.Zip(
List.Transform(
b,
each if List.Count(_) < c then _ & List.Repeat({List.Last(_)}, c - List.Count(_)) else _
)
),
e = List.Select(d, each List.Count(List.Distinct(_)) = List.Count(_)){0}
in
e,
Sol = Table.FromRows(List.Zip(List.Transform(a, each Fx(_))), Table.ColumnNames(S))
in
SolPower Query solution 6 for Generate Unique Day Abbreviations, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChkUnq = (x as list) => List.Count(List.Distinct(x)) = List.Count(x),
Abbs = (x as list, y as number) => List.Transform(x, each Text.Start(_, y)),
MaxLen = (x as list) => List.Max(List.Transform(x, each Text.Length(_))),
UnqLen = (x as list) =>
[
A = List.Transform({1 .. MaxLen(x)}, each if ChkUnq(Abbs(x, _)) then _ else null),
B = List.Min(A)
][B],
FinalFunc = (x as list) => Abbs(x, UnqLen(x)),
Cols = List.Transform(Table.ToColumns(Source), each FinalFunc(_)),
Res = Table.FromColumns(Cols, Table.ColumnNames(Source))
in
ResPower Query solution 7 for Generate Unique Day Abbreviations, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromColumns(
List.Transform(
Table.ToColumns(Source),
(f) =>
[
l = List.Max(List.Transform(f, Text.Length)),
fin = List.Select(
List.Transform(
{1 .. l},
(x) => List.Distinct(List.Transform(f, (y) => Text.Start(y, x)))
),
each List.Count(_) = 7
){0}
][fin]
),
Table.ColumnNames(Source)
)
in
ResultSolving the challenge of Generate Unique Day Abbreviations with Excel
Excel solution 1 for Generate Unique Day Abbreviations, proposed by Bo Rydobon 🇹🇭:
=LEFT(
A3:C9,
BYCOL(
A3:C9,
LAMBDA(
a,
XMATCH(
7,
BYCOL(
LEFT(
a,
SEQUENCE(
,
9
)
),
LAMBDA(
x,
ROWS(
UNIQUE(
x
)
)
)
)
)
)
)
)Excel solution 2 for Generate Unique Day Abbreviations, proposed by John V.:
=LEFT(A3:C9,BYCOL(A3:C9,LAMBDA(d,XMATCH(7,BYCOL(LEFT(d,COLUMN(A:G)),LAMBDA(c,ROWS(UNIQUE(c))))))))Excel solution 3 for Generate Unique Day Abbreviations, proposed by 🇰🇷 Taeyong Shin:
=LET(
F,
LAMBDA(
F,
x,
LET(
c,
COLUMNS(
x
),
IF(
c>1,
HSTACK(
F(
F,
TAKE(
x,
,
c/2
)
),
F(
F,
DROP(
x,
,
c/2
)
)
),
LET(
R,
LAMBDA(
R,
d,
i,
LET(
x,
LEFT(
d,
i
),
IF(
ROWS(
d
)=ROWS(
UNIQUE(
x
)
),
x,
R(
R,
d,
i+1
)
)
)
),
R(
R,
x,
1
)
)
)
)
),
F(
F,
A3:C9
)
)Excel solution 4 for Generate Unique Day Abbreviations, proposed by Kris Jaganah:
=VSTACK(
{"English",
"Catalan",
"Hebrew"},
DROP(
REDUCE(
"",
{1,
2,
3},
LAMBDA(
v,
w,
HSTACK(
v,
LET(
a,
A3:C9,
b,
MAX(
LEN(
a
)
),
c,
REPLACE(
INDEX(
a,
,
w
),
SEQUENCE(
,
b
)+1,
b,
""
),
TAKE(
FILTER(
c,
BYCOL(
c,
LAMBDA(
x,
ROWS(
UNIQUE(
x
)
)
)
)=7
),
,
1
)
)
)
)
),
,
1
)
)Excel solution 5 for Generate Unique Day Abbreviations, proposed by Julian Poeltl:
=LET(D,A3:C9,DROP(REDUCE(0,SEQUENCE(COLUMNS(D)),LAMBDA(A,B,HSTACK(A,MID(CHOOSECOLS(D,B),1,XMATCH(7,BYCOL(MID(CHOOSECOLS(D,B),1,SEQUENCE(,10)),LAMBDA(A,ROWS(UNIQUE(A))))))))),,1))Excel solution 6 for Generate Unique Day Abbreviations, proposed by Timothée BLIOT:
=MID(
A3:C9,
1,
BYCOL(
A3:C9,
LAMBDA(
z,
LET(
S,
SEQUENCE(
MAX(
LEN(
z
)
)
),
MIN(
FILTER(
S,
MAP(
S,
LAMBDA(
x,
LET(
M,
MAP(
z,
LAMBDA(
y,
MID(
y,
1,
x
)
)
),
ROWS(
UNIQUE(
M
)
)=ROWS(
M
)
)
)
)
)
)
)
)
)
)Excel solution 7 for Generate Unique Day Abbreviations, proposed by Hussein SATOUR:
=DROP(
REDUCE(
"",
{1,
2,
3},
LAMBDA(
x,
y,
LET(
I,
INDEX,
a,
LEFT(
I(
A3:C9,
,
y
),
SEQUENCE(
,
MAX(
LEN(
I(
A3:C9,
,
y
)
)
)-1
)
),
HSTACK(
x,
I(
a,
,
XMATCH(
7,
BYCOL(
a,
LAMBDA(
z,
COUNTA(
UNIQUE(
z
)
)
)
)
)
)
)
)
)
),
,
1
)Excel solution 8 for Generate Unique Day Abbreviations, proposed by Sunny Baggu:
=LET(
_e1, LAMBDA(rng,
LET(
_c, SEQUENCE(, MAX(LEN(rng))),
_a, MID(rng, 1, _c),
_b, BYCOL(_a, LAMBDA(a, ROWS(UNIQUE(a)) = ROWS(a))),
_d, XMATCH(TRUE, _b),
MID(rng, 1, _d)
)
),
HSTACK(_e1(A3:A9), _e1(B3:B9), _e1(C3:C9))
)Excel solution 9 for Generate Unique Day Abbreviations, proposed by Sunny Baggu:
=LET(
_m, MAX(LEN(A3:C9)),
DROP(
REDUCE(
"",
SEQUENCE(COLUMNS(A2:C2)),
LAMBDA(x, y,
HSTACK(
x,
LET(
_c, INDEX(A3:C9, , y),
MID(
_c,
1,
TAKE(
TOCOL(
SEQUENCE(, _m) /
N(
BYCOL(
MID(_c, 1, SEQUENCE(, _m)),
LAMBDA(a, ROWS(a) = ROWS(UNIQUE(a)))
)
),
3
),
1
)
)
)
)
)
),
,
1
)
)Excel solution 10 for Generate Unique Day Abbreviations, proposed by LEONARD OCHEA 🇷🇴:
=LET(
i,
A3:C9,
F,
LAMBDA(
F,
x,
n,
IF(
ROWS(
UNIQUE(
LEFT(
x,
n
)
)
)=7,
n,
F(
F,
x,
n+1
)
)
),
LEFT(
i,
BYCOL(
i,
LAMBDA(
a,
F(
F,
a,
)
)
)
)
)Excel solution 11 for Generate Unique Day Abbreviations, proposed by Md. Zohurul Islam:
=LET(
hdr,
A2:C2,
z,
A3:C9,
w,
HSTACK(
2,
4,
7
),
a,
BYROW(
z,
ARRAYTOTEXT
),
REDUCE(
hdr,
a,
LAMBDA(
x,
y,
VSTACK(
x,
LEFT(
TEXTSPLIT(
y,
", "
),
w
)
)
)
)
)Excel solution 12 for Generate Unique Day Abbreviations, proposed by Pieter de B.:
=LET(
a,
LAMBDA(
b,
REDUCE(
"",
SEQUENCE(
MAX(
LEN(
b
)
)
),
LAMBDA(
x,
y,
IF(
ROWS(
x
)=ROWS(
b
),
x,
UNIQUE(
LEFT(
b,
y
)
)
)
)
)
),
HSTACK(
a(
A3:A9
),
a(
B3:B9
),
C3:C9
)
)Excel solution 13 for Generate Unique Day Abbreviations, proposed by Hamidi Hamid:
=LET(
f,
LAMBDA(
z,
LET(
x,
MID(
z,
SEQUENCE(
,
10
),
1
),
r,
BYROW(
x,
LAMBDA(
a,
ARRAYTOTEXT(
SCAN(
,
a,
CONCAT
)
)
)
),
t,
DROP(
REDUCE(
0,
r,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
",",
)
)
)
),
1
),
n,
TOCOL(
BYCOL(
t,
LAMBDA(
a,
COUNTA(
UNIQUE(
a
)
)
)
)
),
c,
TRANSPOSE(
t
),
h,
HSTACK(
n,
c
),
TRANSPOSE(
DROP(
TAKE(
FILTER(
h,
TAKE(
h,
,
1
)=MAX(
TAKE(
h,
,
1
)
)
),
1
),
,
1
)
)
)
),
HSTACK(
f(
A3:A9
),
f(
B3:B9
),
f(
C3:C9
)
)
)Excel solution 14 for Generate Unique Day Abbreviations, proposed by Asheesh Pahwa:
=LET(
r,
DROP(
REDUCE(
"",
SEQUENCE(
3
),
LAMBDA(
x,
y,
HSTACK(
x,
LET(
l,
LEFT(
INDEX(
A3:C9,
,
y
),
SEQUENCE(
,
9
)
),
b,
BYCOL(
l,
LAMBDA(
a,
ROWS(
UNIQUE(
a
)
)
)
)=ROWS(
A3:A9
),
INDEX(
l,
,
XMATCH(
TRUE,
b
)
)
)
)
)
),
,
1
),
r
)Excel solution 15 for Generate Unique Day Abbreviations, proposed by ferhat CK:
=DROP(REDUCE(0,SEQUENCE(3),LAMBDA(i,j,HSTACK(i,LET(q,LAMBDA(x,LET(a,MID(CHOOSECOLS(A3:C9,j),1,x),ROWS(UNIQUE(XMATCH(a,a)))=ROWS(a))),r,DROP(REDUCE(0,SEQUENCE(MAX(LEN(CHOOSECOLS(A3:C9,j)))),LAMBDA(x,y,HSTACK(x,IFS(q(y),MID(CHOOSECOLS(A3:C9,j),1,y),1=1,"")))),,1),CHOOSECOLS(r,XMATCH(1,N(TAKE(r,1)<>""))))))),,1)Excel solution 16 for Generate Unique Day Abbreviations, proposed by JvdV -:
=LEFT(A3:C9,BYCOL(--REGEXTEST(BYCOL(A3:C9,CONCAT),"([A-Z].{"&ROW(1:9)&"}).*1"),SUM)+2)Excel solution 17 for Generate Unique Day Abbreviations, proposed by Nicolas Micot:
=LET(solveCase;LAMBDA(l_days;l_nbCar;l_recusion;LET(
_truncatedDays;GAUCHE(l_days;l_nbCar);
SI(LIGNES(_truncatedDays)=LIGNES(UNIQUE(_truncatedDays));_truncatedDays;l_recusion(l_days;l_nbCar+1;l_recusion))));
solveCase(A3:A9;1;solveCase))Excel solution 18 for Generate Unique Day Abbreviations, proposed by Gabriel Pugliese:
=LET(
z,
A3:C9,
DROP(
REDUCE(
"",
{1,
2,
3},
LAMBDA(
x,
y,
HSTACK(
x,
LET(
_a,
DROP(
REDUCE(
"",
INDEX(
z,
,
y
),
LAMBDA(
a,
v,
VSTACK(
a,
LEFT(
v,
SEQUENCE(
,
LEN(
v
)
)
)
)
)
),
1
),
_b,
XMATCH(
TRUE,
BYCOL(
_a,
LAMBDA(
c,
COUNTA(
c
)=COUNTA(
UNIQUE(
c
)
)
)
)
),
_c,
LEFT(
INDEX(
z,
,
y
),
_b
),
_c
)
)
)
),
,
1
)
)Solving the challenge of Generate Unique Day Abbreviations with Python
Python solution 1 for Generate Unique Day Abbreviations, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=8)
test = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=8).rename(columns=lambda x: x.split('.')[0])
result = input.apply(lambda col: next(col.str[:i] for i in range(1, len(col[0]) + 1) if len(col.str[:i].unique()) == len(col)))
print(result.equals(test)) # True
Solving the challenge of Generate Unique Day Abbreviations with Python in Excel
Python in Excel solution 1 for Generate Unique Day Abbreviations, proposed by Alejandro Campos:
df = xl("A2:C9", headers=True)
def unique_abbreviations(days):
for length in range(1, max(map(len, days)) + 1):
if len(set(abbr := [day[:length] for day in days])) == len(days):
return abbr
return days
result_df = pd.DataFrame({col: unique_abbreviations(df[col]) for col in df.columns})
Python in Excel solution 2 for Generate Unique Day Abbreviations, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:C9", True)
def MyFun(col):
mxLen = len(max(col, key=len))
for l in range(1, mxLen + 1):
abbr = [i[:l] for i in col]
if len(abbr) == len(set(abbr)):
return abbr
return [i[:mxLen] for i in col]
df = df.apply(MyFun)
df
Python in Excel solution 3 for Generate Unique Day Abbreviations, proposed by Seokho MOON:
def get_day_abb(days):
max_length = max(len(day) for day in days)
for i in range(1, max_length + 1):
abbs = [day[:i] for day in days]
if len(set(abbs)) == len(days):
return abbs
return days
df_abb = pd.DataFrame({language: get_day_abb(df[language]) for language in df.columns})
Solving the challenge of Generate Unique Day Abbreviations with R
R solution 1 for Generate Unique Day Abbreviations, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A2:C9")
test = read_excel(path, range = "D2:F9")
abbreviate = function(column) {
for (i in seq_len(nchar(column[1]))) {
abbrs = substr(column, 1, i)
if (length(unique(abbrs)) == length(column)) {
return(abbrs)
}
}
}
result = input %>%
mutate(across(everything(), ~ abbreviate(.)))
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
R solution 2 for Generate Unique Day Abbreviations, proposed by Seokho MOON:
R
get_day_abb <- function(days) {
max_length <- max(nchar(days))
for (i in 1:max_length) {
abbs <- substr(days, 1, i)
if (length(unique(abbs)) == length(days)) {
return(abbs)
}
}
return(days)
}
df_abb <- df %>%
mutate_all(get_day_abb)
df_abb
Solving the challenge of Generate Unique Day Abbreviations with Excel VBA
Excel VBA solution 1 for Generate Unique Day Abbreviations, proposed by Md. Zohurul Islam:
Sub ExcelBI_ExcelCHallenge638()
Dim nk As Long, k As Long
Dim x, y, z
'headers
Range("E1") = "VBA Solution"
Range("E2:G2").Value = Range("A2:C2").Value
nk = WorksheetFunction.CountA(Range("A3:A10000"))
For k = 1 To nk
x = Range("A" & k + 2).Value
y = Range("B" & k + 2).Value
z = Range("C" & k + 2).Value
'post result
Range("E" & k + 2) = Left(x, 2)
Range("F" & k + 2) = Left(y, 4)
Range("G" & k + 2) = Left(z, 7)
Next k
End Sub
Excel VBA solution 2 for Generate Unique Day Abbreviations, proposed by Nicolas Micot:
Nicolas Micot With VBA:
Function f_solveCase(Plage As Range)
Dim nbCar, words, truncatedWord
nbCar = 1
Do
continue = False
For lig = 1 To UBound(words, 1)
If TruncatedWords.Exists(truncatedWord) Then
continue = True
nbCar = nbCar + 1
Exit For
Else
TruncatedWords.Add truncatedWord, truncatedWord
End If
Next lig
Loop While continue
For i = 1 To TruncatedWords.Count
words(i, 1) = TruncatedWords.Items(i - 1)
Next i
f_solveCase = words
End Function
&&