+ Reply to Thread
Results 1 to 20 of 20

Find first missing number in column

  1. #1
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Find first missing number in column

    Hi all,

    How to find first missing number from column A where numbers which we have to consider are on the end of string. Result should be 3
    Attached Files Attached Files
    Last edited by mtcas; 12-01-2020 at 03:02 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,352

    Re: Find first number

    Why should the result be 3? Where does that come from? My crystal ball has gone away for servicing, so you are going to need to explain what you mean by "numbers which we have to consider are on the end of string".
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first number

    If you remove prefix "0612A" you have only numbers and then 3 is missing. I want to find this number.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,352

    Re: Find first number

    Administrative Note:

    Your thread title doesn't come near to saying what you are trying to do, so ...

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Find first number

    Pls try

    B1 cell , Array Formula , Copy and drag down


    HTML Code: 

  6. #6
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first number

    Ok, but when I extend range to 100 then formula not works

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,352

    Re: Find first number

    Administrative Note:

    Sorry, but your posts do not comply with Rule #6 of our Forum RULES:

    Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction, then send a private message to them asking for clarification.

    All Participants:

    Please do not post a reply in a thread where a Moderator or Administrator has requested an action that has not yet been complied with (e.g. title change, code tags requested, etc.). Thanks.


    Waiting for the title change requested. No further posts until this is done, please.
    Last edited by AliGW; 12-01-2020 at 03:00 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,352

    Re: Find first missing number in column

    That's better - thank you. I will now reinstate the posts.

  9. #9
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Find first missing number in column

    Increase the range $A$1:$A$6 to $A$1:$A$100


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  10. #10
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find first missing number in column

    Hi mtcas,

    =IFERROR(AGGREGATE(15,6,ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,"")))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$6,1,5,"")),ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,""))))))),ROWS(B$1:B1)),"")

  11. #11
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first missing number in column

    As I wrote above, I extend range and not working

  12. #12
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first missing number in column

    Quote Originally Posted by Besserwisser View Post
    Hi mtcas,

    =IFERROR(AGGREGATE(15,6,ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,"")))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$6,1,5,"")),ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$6,1,5,""))))))),ROWS(B$1:B1)),"")
    Also not working when extend range to 100

  13. #13
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find first missing number in column

    Hi Mtcas,

    =IFERROR(AGGREGATE(15;6;ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$100;1;5;0));ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))));ROWS(B$1:B1));"")


    regards


    Christian

  14. #14
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Find first missing number in column

    First of all, I want to know the total length of your characters is 6 digits ? 0612A1
    if it is expanded to $A$1:$A$100 so many meaningless 1~9, my formula can be shorter

    {=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$9),--RIGHT($A$1:$A$100),)=ROW($1:$9)),ROW($1:$9)),ROW(A1)),"")}

  15. #15
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first missing number in column

    Quote Originally Posted by Besserwisser View Post
    Hi Mtcas,

    =IFERROR(AGGREGATE(15;6;ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))/NOT(FREQUENCY(--(REPLACE($A$1:$A$100;1;5;0));ROW(INDIRECT("A1:A"&MAX(--(REPLACE($A$1:$A$100;1;5;0)))))));ROWS(B$1:B1));"")


    regards


    Christian
    Now, when I add missing number ex "0612A3", formula show nothing but should 8

  16. #16
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first missing number in column

    Quote Originally Posted by wk9128 View Post
    First of all, I want to know the total length of your characters is 6 digits ? 0612A1
    if it is expanded to $A$1:$A$100 so many meaningless 1~9, my formula can be shorter

    {=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$9),--RIGHT($A$1:$A$100),)=ROW($1:$9)),ROW($1:$9)),ROW(A1)),"")}
    Seems to be ok, thank you

  17. #17
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Find first missing number in column

    another option, perhaps:

    =AGGREGATE(15;6;ROW($1:$9)/ISNA(MATCH("*"&ROW($1:$9);$A$1:$A$100;0));1)

    the above would return 3, or 8 if 0612A3 were added.

  18. #18
    Forum Contributor
    Join Date
    03-14-2017
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    107

    Re: Find first missing number in column

    Quote Originally Posted by XLent View Post
    another option, perhaps:

    =AGGREGATE(15;6;ROW($1:$9)/ISNA(MATCH("*"&ROW($1:$9);$A$1:$A$100;0));1)

    the above would return 3, or 8 if 0612A3 were added.
    Nice and shorter, thanks

  19. #19
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find first missing number in column

    In D2 ARRAY formula, then copy down for more missing numbers. If required change the range A1:A10
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  20. #20
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Find first missing number in column

    if the the digest are from 1 to 9 then shorter is:

    =IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$9);--(RIGHT($A$1:$A$100));0));ROW($1:$9));ROW(A1));"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find first number that is smaller than some number but divisible with other number
    By beard in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 07-20-2019, 12:24 PM
  2. [SOLVED] Not Returning Row Number With FIND statement When FIND should be valid
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2019, 02:22 PM
  3. Replies: 7
    Last Post: 09-23-2017, 10:57 AM
  4. [SOLVED] Find Specific Number - If not found find next lowest number
    By mattyp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-14-2017, 03:03 PM
  5. Replies: 1
    Last Post: 04-10-2017, 03:33 PM
  6. [SOLVED] Find match, find related cell and return that number
    By HelpHelpHelp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2012, 06:46 AM
  7. Replies: 7
    Last Post: 12-18-2008, 07:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1