Making a numbered list and inserting blanks

Lets see if I can explain this well enough....

I have a list of member numbers ie:

0001 green
0002 smith
0007 johnson
0010 jones

Notice these are in order, but there are no numbers or spaces between 0002 and 0007. The end result of my spreadsheet needs to show me ALL the numbers inbetween with blank spaces as their "name". ie:

0001 green
0002 smith
0003
0004
0005
0006
0007 johnson
0010 jones

Does anyone know how to do this? I am having to do the entire list the long way, by seeking out the blank numbers manually and inserting lines. It is taking forever on a 3000 line list.

Attached is a worksheet with the formula to use, adjust it accordingly to your actual reference cells.

Basically, make a list of all the numbers in numerical order then fill in the formula in the right column.
excelexample6-12.xlsx

Hi ikleler,

I would like to suggest you to separate both this column separately having number and names.


and create a new column having the serial number as u want in an order then put the lookup to pull the Respective names from the column containing names.


Please select star from below if it u r satisfied.