1. Let's say you've started with the following spreadsheet and you need to fill in all the missing sequential ID numbers from 1001 to 1025:
2. What you want to do first is add the entire sequence of ID numbers to the end of the corresponding column (you can do that easily by entering and selecting the first number, and then stretching the bottom-right corner down, wile holding Ctrl):
3. Once you have the entire sequence added to the bottom of the corresponding column, select your entire data range:
4. Next, click on the "Sort A to Z" icon under the "Data" tab (feel free to use the "Sort" button if you need to tweak sorting type or sort-by columns):
5. As you can see below (click to enlarge), the data will be sorted as selected and the empty rows will, conveniently, get placed below* original ones:
* It's important that the empty columns get sorted below the populated ones for next step. If you have selected custom sorting options and this did not happen, try playing around with the sorting, until it does. Default "Sort A to Z" does the trick.
6.Last but not least, you need to get rid of the duplicates - select your data range again and click the "Remove duplicates" button on the same "Data" tab:
7. In the popup that appears, select the column that you are populating with sequential values (which therefore contains the duplicates you added) and click OK:
8. You should see a message not unlike the one below, telling you how many duplicates were removed and the number of unique records that remain (which should match total number of your records if you did everything right):
9. You're done - the spreadsheet should now contain your original values, padded with sequential values that you've added:
Yes this may not look very scientific, but it doesn't require any VB Script or macro tinkering (which to me means - "user friendly") and best of all - this should work on any alphabetically sortable records, not just numeric values - last names, usernames, DNS records, you name it!
I didn't have a chance to test this in earlier versions of Excel yet, but I suspect it should also work. Drop me a line in the comments if you manage to get it work before I get a chance to test! Thanks.