A really simple Excel question, except for someone like me
April 28, 2006 6:46 PM Subscribe
MS Excel question.
Imagine this database: 5000 people, each with a unique identifier from 1 to 5000 (the unique identifier is column A). The person who worked with my database has changed it by deleting about a tenth of the rows. I need to insert those rows back. The missing rows as such are easy to identify in a sense, because the unique identifier in column A is not there. (That is, for instance, the first 7 cells in column A are 1, 2, 3, 5, 8, 9, 10; thus, I know that the rows that I need to insert are 4, 6, and 7.) I need to insert about 500 of these rows back. They need to have the unique identifier back in column A (which is easy in one sense because all the identifiers are every integer in a series, that is, 1 to 5000), but rows B to Z, etc., should be empty. Is there a way to automate this process, or do I need to reconcile myself to a couple of hours of doing this automatically, in which I will doubtless insert a couple of errors?
Imagine this database: 5000 people, each with a unique identifier from 1 to 5000 (the unique identifier is column A). The person who worked with my database has changed it by deleting about a tenth of the rows. I need to insert those rows back. The missing rows as such are easy to identify in a sense, because the unique identifier in column A is not there. (That is, for instance, the first 7 cells in column A are 1, 2, 3, 5, 8, 9, 10; thus, I know that the rows that I need to insert are 4, 6, and 7.) I need to insert about 500 of these rows back. They need to have the unique identifier back in column A (which is easy in one sense because all the identifiers are every integer in a series, that is, 1 to 5000), but rows B to Z, etc., should be empty. Is there a way to automate this process, or do I need to reconcile myself to a couple of hours of doing this automatically, in which I will doubtless insert a couple of errors?
Response by poster: Windows.
Jeb, I appreciate the help, but this does not really solve the meat of the problem. It is easy to identify the missing rows with your column fill suggestion. However, I still have to insert about 400 blank cells from cx to zx. I was wondering if there is a way to automate this.
posted by Mr. Justice at 7:03 PM on April 28, 2006
Jeb, I appreciate the help, but this does not really solve the meat of the problem. It is easy to identify the missing rows with your column fill suggestion. However, I still have to insert about 400 blank cells from cx to zx. I was wondering if there is a way to automate this.
posted by Mr. Justice at 7:03 PM on April 28, 2006
Best answer: This is how I'd do it - on a new sheet put 1 to 5000 in column A, then in cell B1 it would be:
=match($A1,sheet1!$a$1:$a$5000,0)
It'll give you an "#N/A" if it isn't in the orginal list. Then sort this new list by column B. There's your missing numbers.
posted by milkrate at 7:03 PM on April 28, 2006
=match($A1,sheet1!$a$1:$a$5000,0)
It'll give you an "#N/A" if it isn't in the orginal list. Then sort this new list by column B. There's your missing numbers.
posted by milkrate at 7:03 PM on April 28, 2006
(And paste those to the bottom of the list and re-sort the whole list by the identifier)
posted by milkrate at 7:06 PM on April 28, 2006
posted by milkrate at 7:06 PM on April 28, 2006
Response by poster: Milkrate: you are an Excel god.
posted by Mr. Justice at 7:59 PM on April 28, 2006
posted by Mr. Justice at 7:59 PM on April 28, 2006
This thread is closed to new comments.
posted by jeb at 6:55 PM on April 28, 2006