+ Reply to Thread
Results 1 to 6 of 6

Finding next available number in range of numbers.

  1. #1
    Registered User
    Join Date
    08-19-2020
    Location
    Aus
    MS-Off Ver
    2020
    Posts
    2

    Finding next available number in range of numbers.

    I have a list as follows:
    Column C:
    1
    2
    3
    7
    8
    9
    12
    13
    14
    15
    30
    31

    How can I generate a list of all available numbers between 1 - 31 starting with the first available (4).

    Thanks for your time.

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Finding next available number in range of numbers.

    Give this array-entered** formula a try...

    =MIN(IF(C1:C11+1<>C2:C12,C1:C11+1))

    **Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself.

    Note: C11 is the next to last cell with data and, of course, C12 is the last cell with data.

    EDIT NOTE: I just realized the above is not what you asked for.. it only finds the next available number, not all of the available numbers. I am not sure how find all of them with a formula... I can do it with VBA macro if that would be acceptable.
    Last edited by Rick Rothstein; 08-19-2020 at 03:27 AM.

  3. #3
    Registered User
    Join Date
    08-19-2020
    Location
    Aus
    MS-Off Ver
    2020
    Posts
    2

    Re: Finding next available number in range of numbers.

    Quote Originally Posted by Rick Rothstein View Post
    Give this array-entered** formula a try...

    =MIN(IF(C1:C11+1<>C2:C12,C1:C11+1))

    **Commit this formula using CTRL+SHIFT+ENTER and note just Enter by itself.

    Note: C11 is the next to last cell with data and, of course, C12 is the last cell with data.

    EDIT NOTE: I just realized the above is not what you asked for.. it only finds the next available number, not all of the available numbers. I am not sure how find all of them with a formula... I can do it with VBA macro if that would be acceptable.
    Thanks for the reply!

    Is it possible to do this for the whole column, so when a new row is added, it refreshes the next available number?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Finding next available number in range of numbers.

    It establish a list of available number in C1:C100:
    In E1:
    Please Login or Register  to view this content.
    Drag down
    Attached Images Attached Images
    Quang PT

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Finding next available number in range of numbers.

    Hello Aaron,

    try this in D1, then copy down.

    =AGGREGATE(15,6,ROW(C$1:C$31)/ISNA(MATCH(ROW(C$1:C$31),C:C,0)),ROWS(D$1:D1))

    @bebo021999,

    use of 1-COUNTIF will give incorrect if numbers repeating, you may need to change to (COUNTIF(...)=0)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Finding next available number in range of numbers.

    For O365 users:

    =FILTER(SEQUENCE(A12,,A1),ISNA(MATCH(SEQUENCE(A12,,A1),A1:A12,0)))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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. Finding a range of numbers in a column of numbers
    By charliemacdmv in forum Excel General
    Replies: 1
    Last Post: 10-17-2019, 09:42 PM
  2. [SOLVED] Finding Max number within a specific range of numbers
    By sjjna in forum Excel General
    Replies: 8
    Last Post: 04-03-2015, 10:20 AM
  3. Finding TWO numbers from a range of numbers to match a target value
    By Eero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2014, 10:08 AM
  4. Replies: 0
    Last Post: 01-29-2012, 10:05 PM
  5. Finding EVEN numbers within a three number cell
    By khank in forum Excel General
    Replies: 10
    Last Post: 02-03-2011, 03:25 PM
  6. Excel 2007 : Finding Lowest Number in a range of numbers.
    By stevenson08 in forum Excel General
    Replies: 7
    Last Post: 11-11-2008, 08:19 AM
  7. Finding what numbers equal another number
    By all2sober in forum Excel General
    Replies: 1
    Last Post: 06-26-2008, 01:54 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