fill down empty cells at every change in value

[Rose]

I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

 

[Bob Bridges]

If this is a one-time effort, Rose, it's not hard. Say your paths are in
column 4. In a helper column to the right use this formula:

=IF(RC4="",R[-1]C4,RC4)

If you're an A1 monoglot, that looks like this in A1 format:

=IF(D2="",D1,D2)

This fills into the helper column the actual value in col 4, or the value
from the above row if col 4 is blank. Once you have these, just copy the
result into col 4 using Paste.Special.Values.

 

[Rose]

That only works for the row that has the path (because it returns a false)
and the following row (which returns a true). After that, the 3rd row is
looking for what's in the 4th column on the above (2nd) row, which is still
blank, so it returns a blank.

If this is a one-time effort, Rose, it's not hard. Say your paths are in
column 4. In a helper column to the right use this formula:

=IF(RC4="",R[-1]C4,RC4)

If you're an A1 monoglot, that looks like this in A1 format:

=IF(D2="",D1,D2)

This fills into the helper column the actual value in col 4, or the value
from the above row if col 4 is blank. Once you have these, just copy the
result into col 4 using Paste.Special.Values.

I have a very large spreadsheet where 1 column is a folder path. One path
will be listed, then a random number of blank cells are below it until a
different path is found. What I'm looking for is a command to make the path
fill down into the empty cells below it until it finds a new value, then fill
that value down, etc.

 

[Gord Dibben]

Select the column with the blanks then F5>Special>Blanks>OK

Type an = sign in active blank cell then point or arrow up to cell above and
hit CTRL + ENTER to fill blanks.

Copy>Paste Special>Values>OK>Esc


Gord Dibben MS Excel MVP

 

[Rose]

That was it - thank you!

Select the column with the blanks then F5>Special>Blanks>OK

Type an = sign in active blank cell then point or arrow up to cell above and
hit CTRL + ENTER to fill blanks.

Copy>Paste Special>Values>OK>Esc


Gord Dibben MS Excel MVP