1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# Answer for "Find out if a number has repetitive digits" Formula Home work

Discussion in 'Ask an Excel Question' started by Kutty, Feb 12, 2018.

1. ### KuttyMember

Messages:
40
is this correct or not...

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))))))))))

File size:
244.1 KB
Views:
15

Messages:
40

File size:
13.1 KB
Views:
7
3. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

A shorter formula , an array formula , would be :

=MAX(FREQUENCY(MATCH(0+MID(A1, ROW(INDIRECT("1:" & LEN(A1))),1),{0,1,2,3,4,5,6,7,8,9},0), {0,1,2,3,4,5,6,7,8,9})) > 1

This is to be entered using CTRL SHIFT ENTER.

A non-array formula would be :

=ISNUMBER(MODE.MULT(MATCH(0+MID(A1, ROW(INDIRECT("1:" & LEN(A1))),1),{0,1,2,3,4,5,6,7,8,9},0)))

Narayan
Thomas Kuriakose and Kutty like this.
4. ### shrivallabhaExcel Ninja

Messages:
1,925
If you copy the MATCH formula block Narayan has demonstrated on the bins_array part of FREQUENCY then the formula will extend capacity to check any duplicate like below (CTRL+SHIFT+ENTER)

=SUM((FREQUENCY(MATCH(MID(A1,ROW(\$A\$1:INDEX(A:A,LEN(A1))),1),MID(A1,ROW(\$A\$1:INDEX(A:A,LEN(A1))),1),0),MATCH(MID(A1,ROW(\$A\$1:INDEX(A:A,LEN(A1))),1),MID(A1,ROW(\$A\$1:INDEX(A:A,LEN(A1))),1),0))>1)+0)>0
Thomas Kuriakose and Kutty like this.
5. ### AliGWActive Member

Messages:
332
Is it usual here to do people's homework for them???

Messages:
40
7. ### KuttyMember

Messages:
40
I'm surprised to see everybody posting their formula in different view. Thanks to Excel Ninjas.
8. ### John Jairo VWell-Known Member

Messages:
512
Hi, to all!

Another options could be (without CSE):
=LEN(A1)>COUNT(INDEX(FIND(ROW(1:10)-1,A1),))
=LEN(A1)>COUNT(FIND({0,1,2,3,4}+{0;5},A1))
<-- Lori's formula.

Blessings!