List the numbers which have got any digits repeated. For example in 3545, 5 has been repeated two times. 80473 – No digit is repeated.. 10980048 – 0 & 8 have been repeated Your formula need not be different from others as long as you have worked out your formula independently)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 64
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Detect Repeated Digits in Numbers with Power Query
Power Query solution 1 for Detect Repeated Digits in Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calc = Table.AddColumn(
Source,
"Calc",
each [
Text = Text.From([Numbers]),
ToList = Text.ToList(Text),
Unique = List.Distinct(ToList),
Combine = Text.Combine(Unique),
Check = Text.Length(Text) <> Text.Length(Combine)
][Check]
),
Return = Table.SelectRows(Calc, each [Calc])[[Numbers]]
in
ReturnPower Query solution 2 for Detect Repeated Digits in Numbers, proposed by Luan Rodrigues:
let
Fonte = Data,
Result = Table.SelectRows(
Table.AddColumn(
Fonte,
"validar",
each [
a = Text.ToList(Text.From([Numbers])),
b = List.Count(a),
c = List.Count(List.Distinct(a)),
Result = List.Select({b, c}, each b <> c)
][Result]{0}?
),
each [validar] <> null
)[[Numbers]]
in
ResultPower Query solution 3 for Detect Repeated Digits in Numbers, proposed by Alexis Olson:
Table.SelectRows(Source, each not List.IsDistinct(Text.ToList(Text.From([Numbers]))))Power Query solution 4 for Detect Repeated Digits in Numbers, proposed by Brian Julius:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WslCK1YlWMjUzB9OGRsYmxlAWkGkKZhobwKSBtLm5hQGYbWkMZBkZgtkWBpZApBQbCwA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Numbers = _t]
),
Duplicate = Table.TransformColumns(
Table.DuplicateColumn(Source, "Numbers", "Dupe"),
{"Dupe", Text.ToList}
),
Expand = Table.ExpandListColumn(Duplicate, "Dupe"),
Group = Table.RemoveColumns(
Table.SelectRows(
Table.Group(
Expand,
{"Numbers"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"CountDistinct", each Table.RowCount(Table.Distinct(_)), Int64.Type}
}
),
each [Count] <> [CountDistinct]
),
{"Count", "CountDistinct"}
),
Rename = Table.RenameColumns(Group, {"Numbers", "Answer Expected"})
in
RenamePower Query solution 5 for Detect Repeated Digits in Numbers, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "RepeatedDigit"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each [Numbers] <> Number.From(Text.Combine(List.Distinct(Text.ToList(Text.From([Numbers])))))
)
in
#"Filtered Rows"
Explanations and comparison of 3 variants:
https://www.linkedin.com/posts/matthiasfriedmann_excel-excelchallenge-powerquerychallenge-activity-6997852769470586880-ubDZ
Power Query solution 6 for Detect Repeated Digits in Numbers, proposed by Venkata Rajesh:
let
Source = Data,
Output = List.Select(
Source[Numbers],
each
let
_row = Text.ToList(Text.From(_))
in
List.Count(_row) <> List.Count(List.Distinct(_row))
)
in
OutputPower Query solution 7 for Detect Repeated Digits in Numbers, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Numbers", type text}}),
Final = [
CountOriginal = Table.AddColumn(
ChangedType,
"CountInitial",
each List.Count((Text.ToList([Numbers])))
),
CountDigit = Table.AddColumn(
CountOriginal,
"CountDistinct",
each List.Count(List.Distinct(Text.ToList([Numbers])))
),
Comparison = Table.AddColumn(
CountDigit,
"ComparisonColumn",
each [CountInitial] = [CountDistinct]
),
Selection = Table.SelectRows(Comparison, each [ComparisonColumn] = false),
OnlyNumbers = Table.SelectColumns(Selection, {"Numbers"})
][OnlyNumbers]
in
FinalSolving the challenge of Detect Repeated Digits in Numbers with Excel
Excel solution 1 for Detect Repeated Digits in Numbers, proposed by Rick Rothstein:
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,IFERROR(MODE(-MID(x,SEQUENCE(LEN(x)),1)),))))Excel solution 2 for Detect Repeated Digits in Numbers, proposed by John V.:
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,LET(d,-MID(x,SEQUENCE(LEN(x)),1),OR(FREQUENCY(d,d)>1)))))
or (shorten Tae yong Shin... i know he don't like shortening formulas - nice idea! ):
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,COUNT(FIND(ROW(1:10)-1,x))<>LEN(x))))Excel solution 3 for Detect Repeated Digits in Numbers, proposed by محمد حلمي:
ExampleExcel solution 4 for Detect Repeated Digits in Numbers, proposed by 🇰🇷 Taeyong Shin:
=FILTER(A2:A9, MAP(A2:A9, LAMBDA(m, COUNT(FIND(SEQUENCE(10)-1, m))<>LEN(m) )) )Excel solution 5 for Detect Repeated Digits in Numbers, proposed by 🇰🇷 Taeyong Shin:
=TOCOL(REGEXEXTRACT(A2:A9,".*?(d).*?1.*"),2)Excel solution 6 for Detect Repeated Digits in Numbers, proposed by Julian Poeltl:
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(A,LEN(A)<>ROWS(UNIQUE(MID(A,SEQUENCE(LEN(A)),1))))))Excel solution 7 for Detect Repeated Digits in Numbers, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A9,
MAP(
A2:A9,
LAMBDA(
a,
OR(
LEN(
a
) - LEN(
SUBSTITUTE(
a,
SEQUENCE(
10,
,
0
),
""
)
) > 1
)
)
)
)Excel solution 8 for Detect Repeated Digits in Numbers, proposed by Timothée BLIOT:
=LET(Numbers,
A2:A9,
Digits,
IFERROR(
TEXTSPLIT(
TEXTJOIN(
"/",
1,
BYROW(
Numbers,
LAMBDA(
a,
TEXTJOIN(
".",
1,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)
)
),
".",
"/",
1
),
"#"
),
Duplicates,
MAKEARRAY(ROWS(
Digits
),
COLUMNS(
Digits
),
LAMBDA(a,
b,
SUMPRODUCT(IF(INDEX(
Digits,
a,
b
)<>"#",
1*(INDEX(
Digits,
a
)=INDEX(
Digits,
a,
b
)),
0)))),
RepeatedChar,
IF(
Duplicates >=2,
1,
0
),
FILTER(
Numbers,
BYROW(
RepeatedChar,
LAMBDA(
a,
IF(
SUM(
a
)>=1,
1,
0
)
)
),
""
))Excel solution 9 for Detect Repeated Digits in Numbers, proposed by Stefan Olsson:
=ArrayFormula(FILTER(A2:A9,REGEXMATCH(A2:A9&"","(0.*0)|(1.*1)|(2.*2)|(3.*3)|(4.*4)|(5.*5)|(6.*6)|(7.*7)|(8.*8)|(9.*9)")))Excel solution 10 for Detect Repeated Digits in Numbers, proposed by El Badlis Mohd Marzudin:
=FILTER(A2:A9,
MAP(A2:A9,
LAMBDA(a,
LEN(
CONCAT(
UNIQUE(
MID(a,SEQUENCE(LEN(a)),1)))) <> LEN(a))))Excel solution 11 for Detect Repeated Digits in Numbers, proposed by Paolo Pozzoli:
=LET(
nums;
A2:A9;
bools;
MAP(
nums;
LAMBDA(
n;
NON(
LUNGHEZZA(
n
)=CONTA.VALORI(
UNICI(
STRINGA.ESTRAI(
n;
SEQUENZA(
LUNGHEZZA(
n
)
);
1
)
)
)
)
)
);
out;
FILTRO(
nums;
bools
);
out
)
without LET (more concise)
=FILTRO(
A2:A9;
MAP(
A2:A9;
LAMBDA(
n;
NON(
LUNGHEZZA(
n
)=CONTA.VALORI(
UNICI(
STRINGA.ESTRAI(
n;
SEQUENZA(
LUNGHEZZA(
n
)
);
1
)
)
)
)
)
)
)Excel solution 12 for Detect Repeated Digits in Numbers, proposed by Mohamed Helmy:
=LET(
a,
A2:A9,
FILTER(
a,
MMULT(
IFERROR(
FIND(
SEQUENCE(
,
10
)-1,
a
)^0,
),
ROW(
1:10
)^0
)<>LEN(
a
)
)
)Excel solution 13 for Detect Repeated Digits in Numbers, proposed by RIJESH T.:
=LET(n,A2:A9,FILTER(n,NOT(n=MAP(n,LAMBDA(a,CONCAT(UNIQUE(MID(a,ROW(1:8),1)))))+0)))Excel solution 14 for Detect Repeated Digits in Numbers, proposed by Rajesh Sinha:
=IF(MAX(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))>1,1,0)
Then in C2 =FILTER($A$2:$A$9,($B$2:$B$9=1),)Excel solution 15 for Detect Repeated Digits in Numbers, proposed by Stevenson Yu:
=LET(
A,
A2:A9,
FILTER(
A,
BYROW(
A,
LAMBDA(
Q,
LET(
A,
Q,
C,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
E,
MAX(
BYROW(
N(
C=TOROW(
C
)
),
LAMBDA(
X,
SUM(
X
)
)
)
)>1,
E
)
)
)
)
)Solving the challenge of Detect Repeated Digits in Numbers with Python in Excel
Python in Excel solution 1 for Detect Repeated Digits in Numbers, proposed by Alejandro Campos:
numbers = xl("A2:A9")[0]
def has_repeated_digits(number):
digits = str(number)
return len(digits) != len(set(digits))
[num for num in numbers if has_repeated_digits(num)]