Is there a formula I can use to find gaps in sequential numbers and fill in the missing ones?
For example, if I have the series: 1000, 1001, 1004, 1009 and I want the range to be from 1000 to 1010, is there a formula that I can write to detect the missing numbers and add them in?
As Yellowbox noted, a formula cannot insert rows, or values into other fields, so that's not going to be an option.
It may be possible to add a (hidden) column of 'expected' values and highlight the cells that don't match, which would give you a visual cue as to which cells/rows need amending, but you'd have the make the changes yourself.
Alternatively a script could check your table and insert additional rows/cells as needed, but it would take more clarification of what the final outcome should look like to be able to determine feasibility.
Additional context will help us help you. Is there data in other columns that need to remain aligned? or are you just trying to create a sequence in a single column?
If you just want a proper sequence in a column, then you can type in the first two entries...
select the first cell and type "1000"
then select the next cell below and type "1001"
now select the two cells, then hover the cursor over the bottom edge. Click, and hold, on the yellow dot (the fill control) and drag down as needed
Hi atomic_chihuahua,
What is your overall aim? A formula can not insert rows into a table. You must insert rows manually.
A screen shot of your table will help us to see what you see. We are not looking over your shoulder.
Regards,
Ian.
Not my chihuahua, found on the web!
Find and fill gaps in sequential numbers using a formula
