Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.
For example, if A1 has 123405, then answer should be FALSE
and if A1 has 123455, then answer should be TRUE
Go ahead and post your answers (formulas, VBA or M script) in the comments section.
Clues and hints
Check out below formula tips to get some clues on how to solve this problem.
- Check if a range has 1 to n numbers
- Check if a range has duplicate values
- One more way to check if a list has duplicate items
More home work:
Why not indulge in some cheese, wine and Excel? That is how I like to enjoy my Tuesdays. Take up these homework problems to work out your formula muscles.
- How many employees are away during that big game?
- Can you solve the blood pressure problem?
- More home work and challenges
57 Responses
=ISNUMBER(MODE(–MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)))
My solution isn’t very elegant, but it works:
=IF(ISERROR(SUM(1*SUBSTITUTE(A1,ROW($A$1:$A$10)-1,”x”,2))),TRUE,FALSE)
Entered as CSE of course
Hi,
There is no need to use IF function in the proposed solution..
ISERROR(SUM(1*SUBSTITUTE(A1,ROW($A$1:$A$10)-1,”x”,2)))
Haha you’re right of course, thanks
for french users :
=ESTNUM(MODE(–STXT(A11;LIGNE(DECALER($A$1;;;NBCAR(A11)));1)))
=(MID(A38,LEN(A38)-1,1)-RIGHT(A38,1))=0
{=LEN(A1)>MIN(LEN(SUBSTITUTE(A1,REPT(ROW($A$1:$A$10)-1,2),””)))}
CSE formula
Repetitive digits is different than duplicate digits. For example, 123454 would have duplicate digits whereas 123445 would have repetitive digits. Which did you want?
Both.
https://twitter.com/dhExcel/status/915238696286347266
What is the right answer?
The easiest way for numbers would be using SUBSTITUTION calculations, such as:
=IF(LEN(B2)-LEN(SUBSTITUTE(B2,”0″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”1″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”2″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”3″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”4″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”5″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”6″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”7″,””))>1,TRUE,
IF(LEN(B2)-LEN(SUBSTITUTE(B2,”8″,””))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,”9″,””))>1,TRUE,FALSE))))))))))
If you want to know which digit had the most, you could simply use the logic portion comparing each number:
=LEN(B2)-LEN(SUBSTITUTE(B2,”5″,””))
This way it doesn’t matter where in the string the duplication of digits occurs, they all will be “counted.”
Similar to Tom’s solution, but array entered to save some typing!
=(LEN(A1)-MIN(LEN(SUBSTITUTE(A1,ROW($A$1:$A$10)-1,””))))<2
Cheers,
Dave
Great! May be you coud use {0,1,2,3,4,5,6,7,8,9} instead ROW() for clarity but it’s a great solution anyway.
Thank you for sharing!
Similar to Tom Smith’s solution, just run through all the cases:
=0<IFERROR(SEARCH("00",B1),0)+IFERROR(SEARCH("11",B1),0)+IFERROR(SEARCH("22",B1),0)+IFERROR(SEARCH("33",B1),0)+IFERROR(SEARCH("44",B1),0)+IFERROR(SEARCH("55",B1),0)+IFERROR(SEARCH("66",B1),0)+IFERROR(SEARCH("77",B1),0)+IFERROR(SEARCH("88",B1),0)+IFERROR(SEARCH("99",B1),0)
As I'm ready to post, it appears this is not what you wanted. Oh well, it may be useful to someone in the future. I know I've found many solutions in the Chandoo comments section.
A simpler solution, but, along the same lines:
=COUNT(SEARCH({“00″,”11″,”22″,”33″,”44″,”55″,”66″,”77″,”88″,”99”}, A1))>0
Note: Only the “00” needs to be a string – otherwise, it’ll turn into a single 0. All the others can be numbers, but, it seemed sloppy to me to have an array of one string and 9 numbers.
=IF(MOD(MID(A1,LEN(A1)-1,2),11)=0,”Duplicate Digits”,””)
This only works if duplicate (repeated) numbers are last 2 digits of the number, ie. 12345677, due to MID(A1, LEN(A1)-1, 2). If the dupicates are the first numbers (112345) or not repeated side by side (123145, note the 1’s) then the formula will return the FALSE result.
The example by chandoo stated only last two digits!
This one too..
=IF(MOD(D3-ROUNDDOWN(D3,-2),11)=0,”Duplicate”,””)
{=IFNA(MATCH(0, MOD(MID(A1&”|”,ROW(INDIRECT(“1:”&LEN(A1)+1)),1)&MID(“|”&A1,ROW(INDIRECT(“1:”&LEN(A1)+1)),1),11), 0)>0, FALSE)}
To determine if there are any duplicates, consecutive or otherwise:
=MAX(FREQUENCY(–MID(A1,ROW(INDIRECT(“1:” & LEN(A1))), 1), {0,1,2,3,4,5,6,7,8,9}))>1
For text and numbers – my proposal (CSE formula)
=OR(FREQUENCY(CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))>1)
I do not know why my formula was cutted in my previous post, so this is next try
=OR(FREQUENCY(CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),
CODE(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))>1)
Here is a code for PQ
let
Source = Excel.CurrentWorkbook(){[Name=”tblNumbers”]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“TextOrNumbers”, type text}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Result”, each List.Distinct(Text.ToList([TextOrNumbers])) Text.ToList([TextOrNumbers]) )
in
#”Added Custom”
hmm… this site cut some part of the code so this is challenge for readers to put in the last step in correct place two signs :-))
Hi Team,
Find the repeated number from cell either true or false by this formula.
=COUNT(SEARCH(REPT({1,2,3,4,5,6,7,8,9},2),A2))>0
Its not a give correct Output when you apply this function on when duplicate number not in consecutive seq.
For Example:-102565
It’s asking about repetitive number means continue like (12344) not like 102565.
that does consecutive digits, a small tweak might do the trick:
=COUNT(SEARCH(REPT({0,1,2,3,4,5,6,7,8,9}&"*",2),A2))>0
One more for the sake of fun
Solution for duplicates or consecutive or otherwise
LEN(A1)>COUNT(IFERROR(SEARCH(ROW(A1:A10)-1,A1),””))
Use below function in Cell b2 and put the number in cell A2.
=MODE(–MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1))
=IFERROR(MODE(–MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1)),””)
Your solution can avoid IFERROR:
With CSE:
=LEN(A1)>COUNT(SEARCH(ROW(1:10)-1,A1))
Without CSE:
=LEN(A1)>COUNT(INDEX(SEARCH(ROW(1:10)-1,A1),))
Blessings!
=LEN(A1) NE COUNT(SEARCH({“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, A1))
=SUMPRODUCT(–(FREQUENCY(VALUE(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),{0,1,2,3,4,5,6,7,8,9})>1))
Thanks John, this is clever
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Split Column by Position” = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{“Column1”, type text}}, “en-IN”), {{“Column1”, Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Column1″),
#”Grouped Rows” = Table.Group(#”Split Column by Position”, {“Column1”}, {{“Count”, each Table.RowCount(_)>1, type number}}),
#”Changed Type” = Table.TransformColumnTypes(#”Grouped Rows”,{{“Count”, type text}}),
#”Added Conditional Column” = Table.AddColumn(#”Changed Type”, “Custom”, each if [Count] = “false” then “0” else “1” ),
#”Changed Type1″ = Table.TransformColumnTypes(#”Added Conditional Column”,{{“Custom”, Int64.Type}}),
#”Calculated Sum” = List.Sum(#”Changed Type1″[Custom])
in
#”Calculated Sum”
another variation (non-cse / non-volatile):
=LEN(A1)>COUNT(FIND({0,1,2,3,4}+{0;5},A1))
This was really very helpful! I got my answer!
It’s interesting how many responses this article got compared to the ones immediately before and after it. It’s not a perfect comparison; other articles perhaps do not invite responses in the same way. Regardless, I would surmise that people enjoy these Excel challenges.
@Black Moses
I have never been able to work out why some posts get huge responses and others get very few.
Posts I have made that I thought were fantastic have been lucky to gets comments, but others seemingly of lesser standard get inundated
Go figure?
Any ideas as to what makes one post more appealing than any others will be appreciated
I guess I’m as guilty as anyone of reading but not commenting,
I, for one, find your formula forensics series incredibly interesting and useful, and your latest post on conditional formatting on chart data labels is a cool trick that I hope I will have opportunity to use, but I rarely comment on articles unless there’s some direct/indirect question posed (doesn’t have to be a challenge, but just something to respond to), that’s pretty much the key for engaging me.
I *could* simply post “that’s awesome, thanks for posting this” when I read something on here I like, which I’m sure would be welcome, but in actual fact wouldn’t do much for advancing meaningful, insightful discourse on the site. And actually me posting “that’s awesome” could simply be replicated with a ‘like’ button a la Facebook.
I have no idea what makes some posts more appealing than others, maybe a ‘like’ button, or a ‘How appealing did you find this post on a scale of 1-10?’ web form straw poll on every article would generate some insight.
It is surprising, but at least it is consistent (ie we get more responses for homework / challenge type questions). Also, over the years I think blog has gone quieter (despite having way more traffic than earlier times). It does feel like an echo chamber sometimes and I am not sure if a certain topic / article is useful to the audience or not. But we march along, one post at a time.
=IF(SUM((FREQUENCY(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1,MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)>1)*1)=1,TRUE,FALSE)
for the fun of it one more
{=SUM(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1),0))=LEN(A1)*(LEN(A1)+1)/2}
=LEN(A1)COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A1))
=LET(x, SEQUENCE(10,1,0), y, IFERROR(FIND(x,A1),””), z, COUNT(y),NOT(z=LEN(A1)))
=AND(ISERR(FIND(“00″,TEXT(A1,”0”))),ISERR(FIND(“11″,TEXT(A1,”0”))),ISERR(FIND(“22″,TEXT(A1,”0”))),ISERR(FIND(“33″,TEXT(A1,”0”))),ISERR(FIND(“44″,TEXT(A1,”0”))),ISERR(FIND(“55″,TEXT(A1,”0”))),ISERR(FIND(“66″,TEXT(A1,”0”))),ISERR(FIND(“77″,TEXT(A1,”0”))),ISERR(FIND(“88″,TEXT(A1,”0”))),ISERR(FIND(“99″,TEXT(A1,”0”))))
I challenged myself to come up with any solution before looking through the comments. I’m blown away by how much I can learn from this post alone. I knew there’d be an easier way out there.
=COUNT(FIND((ROW(1:10)-1),A1))=LEN(A1)
IF(IFERROR(FIND(9,A1)<FIND(9,A1,FIND(9,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(8,A1)<FIND(8,A1,FIND(8,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(7,A1)<FIND(7,A1,FIND(7,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(6,A1)<FIND(6,A1,FIND(6,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(5,A1)<FIND(5,A1,FIND(5,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(4,A1)<FIND(4,A1,FIND(4,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(3,A1)<FIND(3,A1,FIND(3,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(2,A1)<FIND(2,A1,FIND(2,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(1,A1)<FIND(1,A1,FIND(1,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(0,A1)<FIND(0,A1,FIND(0,A1)+1),FALSE)=TRUE,TRUE,FALSE))))))))))
it's too long formula but it's perfect formula for within the cell conditional
if you want excel file i will upload with your permission.
=SUMPRODUCT(–(MID(A1,ROW(INDIRECT(“1:”&LEN(A1)-1)),1)=
MID(A1,ROW(INDIRECT(“2:”&LEN(A1))),1)))>0
Maybe late …
=IF(ISNUMBER(AGGREGATE(15,6,FIND(SEQUENCE(10,,0)&SEQUENCE(10,,0),B5),1)),”Yes”,”No”)
starting from B5
let
Source = Excel.CurrentWorkbook(){[Name=”Table2″]}[Content],
#”Duplicated Column” = Table.DuplicateColumn(Source, “Column1”, “Column1 – Copy”),
#”Split Column by Position” = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(#”Duplicated Column”, {{“Column1 – Copy”, type text}}, “en-GB”), {{“Column1 – Copy”, Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), “Column1 – Copy”),
#”Changed Type” = Table.TransformColumnTypes(#”Split Column by Position”,{{“Column1 – Copy”, type number}, {“Column1″, type text}}),
#”Removed Duplicates” = Table.Distinct(#”Changed Type”),
#”Grouped Rows” = Table.Group(#”Removed Duplicates”, {“Column1”}, {{“Count”, each Table.RowCount(_), Int64.Type}}),
#”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Custom”, each if Text.Length([Column1]) -[Count] = 0 then false else true),
#”Removed Columns” = Table.RemoveColumns(#”Added Custom”,{“Count”})
in
#”Removed Columns”
=LET(x, SEQUENCE(10,1,0), y, IFERROR(FIND(x,A1),””), z, COUNT(y),NOT(z=LEN(A1)))
RegEx solutions for Excel 365:
Any duplicates
=REGEXTEST(A1, “(\d).*(\g1)”)
Any adjacent duplicates
=REGEXTEST(A1, “(\d)(\g1)”)
\d — Match any digit 0-9
.* — Match any text of any length
() — Capture group (allows a match to be referenced later)
(\g1) — Match whatever was matched in capture group 1 (i.e., whatever digit 0-9 was matched, try to match it again)
Feels like cheating compared to the creative solutions offered by others!