Fill down in Excel, but based on multiple values

Question

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!

Answer 1

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.

Answer 2

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...

Answer 3

There is actually an easier way to do this without having to copy the entire row you need to drop down values.

Create a new Column, Use the =IFBLANK() Function and follow the steps similar to an =IF() Function.

Basically =ifblank(Value if true (the cell above), (value if false(same cell))....