+ Reply to Thread
Results 1 to 3 of 3

Automatically fill in missing numbers from a list, adding a cell for each missing number

  1. #1
    Registered User
    Join Date
    06-01-2022
    Location
    London, England
    MS-Off Ver
    Version 2205, OFFICE 365
    Posts
    1

    Automatically fill in missing numbers from a list, adding a cell for each missing number

    Let's say I have an excel Sheet where in the first column is:
    1
    3
    4
    7
    10

    How can I quickly fill up this list with the missing ascending whole numbers such that it reads? An alternative way to put it is that I want to fill in the list of ascending numbers, adding a row for each missing number

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    Many thanks.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,337

    Re: Automatically fill in missing numbers from a list, adding a cell for each missing numb

    With data in A1:A5 i.e.

    1
    3
    4
    7
    10

    in B1

    =A1

    in B2

    =IF($B1<MAX($A$1:$A$5),B1+1,"")

    copy down
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Registered User
    Join Date
    03-01-2022
    Location
    Eire
    MS-Off Ver
    O365
    Posts
    57

    Re: Automatically fill in missing numbers from a list, adding a cell for each missing numb

    or, since you have O365:

    =SEQUENCE(MAX(A1:A5),,MIN(A1:A5))

    if the sequence always starts with 1 (even if it is missing from the list):

    =SEQUENCE(MAX(A1:A5))

+ 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. Fill missing hours automatically excel
    By johnpit in forum Excel General
    Replies: 1
    Last Post: 08-17-2021, 07:17 AM
  2. Fill in missing numbers in sequence
    By haroldlarold in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-28-2016, 12:49 AM
  3. Find missing number and move all following numbers to fill the gap
    By Bezzie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-24-2015, 11:02 AM
  4. Adding rows and filling in missing numbers
    By flatlander88 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2013, 01:39 PM
  5. [SOLVED] Find missing number and copy only missing number to another coloumn
    By vijaynadiad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-20-2013, 02:01 PM
  6. Adding missing numbers in a Pivot chart
    By nigo75 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-12-2013, 12:10 AM
  7. Fill Numbers Missing In A Range
    By dannyfromnj in forum Excel General
    Replies: 1
    Last Post: 01-04-2007, 06:24 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