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.
6 comments:
Excellent and elegant solution to a vexatious problem. Thanks!!!!
This is really cool, but when I use it, it always delete the duplicated rows that contains useful information (i.e. the last names and first names). How can I fix that?
This is an elegant solution suitable for VBA illiterate like myself :) But I always end up deleting those that contains useful info (i.e. the first and last names) when I try to do this. Any solutions? Thanks heaps!
Hi Heidi, for the "remove duplicates" function to delete the empty lines, they need to get sorted *below* the populated ones. Just play around with the custom sorting options until that happens - then the "remove duplicates" function will delete the correct (empty) lines. I found that using the "Data" - "Sort Smallest to Largest" (the "AZ↓" icon - see screenshot in step 4) on the column you're adding values to, then selecting "Expand the selection" works best. Hope this works!
now that was awesome.. thank you very much!!
Thank you very much - a simple and logical solution explained in clear English. Just what I was looking for!
Post a Comment