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?
posted by Mr. Justice to Computers & Internet (6 answers total)
 
Insert a column before a, now what was a is b. In a2, put something like (=b2-b1). Paste that all the way down. The rows that do not have '1' in column A are the boundaries of the skipped rows. use autofilter to find these quickly.
posted by jeb at 6:55 PM on April 28, 2006


Mac or Win?
posted by bytemover at 6:56 PM on April 28, 2006


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


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


(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


Milkrate: you are an Excel god.
posted by Mr. Justice at 7:59 PM on April 28, 2006


« Older Sightseeing on I-10   |   best laserjet printer? Newer »
This thread is closed to new comments.