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. Kutty

    Kutty Member

    Messages:
    34
    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))))))))))

    please tell me.

    Attached Files:

    • 1.jpg
      1.jpg
      File size:
      244.1 KB
      Views:
      15
  2. Kutty

    Kutty Member

    Messages:
    34
    I think it will help you to understand clearly

    Attached Files:

  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,565
    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. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,865
    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. AliGW

    AliGW Active Member

    Messages:
    127
    Is it usual here to do people's homework for them??? :(
  6. Kutty

    Kutty Member

    Messages:
    34
  7. Kutty

    Kutty Member

    Messages:
    34
    I'm surprised to see everybody posting their formula in different view. Thanks to Excel Ninjas.
  8. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    452
    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!

Share This Page