Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have spreadsheets (not created by me) that have blank entries in one column where they should really have data. I want to take every empty cell and fill it with the nearest value above it. I'm looking for as little manual intervention as possible, because I'll have to do it repeatedly.

I thought some previous version of Excel, or maybe another spreadsheet from the distant past, would do this by default -- that is, if you selected the column with foo and bar, and chose the equivalent of "fill down", you would get what's in the WANT column. What I actually get in Excel is the GET column.

HAVE:            WANT:           GET:
foo     1        foo     1       foo     1
        2        foo     2       foo     2
bar     1        bar     1       foo     1
        2        bar     2       foo     2
        3        bar     3       foo     3

I'm worried that this might need a macro to be done properly. I used to be a whiz with Excel macros, and then suddenly they were all in VB.

My fallback position will be to dump the whole thing to CSV and write a Python script, but if there's any way to do it in Excel that would be much preferable. Even if it involves a couple of different manual steps, that's fine; just not one step per group of lines. That is, a process of "copy the column, do X to it, cut and paste it back" would work, but "do X for each occurrence of foo or bar" won't. The files are too big for that.

Any thoughts are appreciated!

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Mr. Excel has the answer for pre-Ribbon versions of Excel. Below are instructions for Excel 2007. With foo/bar in column A and your data in column B:

  1. Select all of the data in column A, from top to bottom.
  2. Click on Home->Find & Select->Go To Special
  3. Click "Blanks" and hit "Ok". (At this point, all blanks in the column should be selected, and cell A2 should be the active cell.)
  4. Press "=", and then press up to select cell A1.
  5. Press CtrlEnter to copy the formula into all selected cells.

Optional Step 7: If you need only the values, copy the whole column and "Paste Special" just values over top.

share|improve this answer
    
This didn't work exactly as presented, but the referenced page got me there. Thanks! Sorry for not responding for so long -- stackoverflow sends me email but apparently superuser doesn't. –  Jenn D. Jul 28 '10 at 14:38
    
I just happened to need to do this again, and decided to try again. I had no difficulties. Do you know which step(s) caused problems for you? –  technomalogical Jul 29 '10 at 2:41

To do it manually:

Add a column (B) between the foo-bar column (say A) and the number column (now C).

Formula in B1 is something like =A1, in B2 (and copied down to the end of the data) =if(A2="",B1,A2). Column B now has the foo-bars almost as you want them, but they are the result of formulae rather than text.

Copy column B and paste special it into column A as values.

You can now delete column B.

If you get the idea, it's probably quicker to do it than write down the description, but whether you would prefer a Python script or not will depend on the number of spreadsheets...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.