Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have some text that I've taken in from a pdf that isn't exactly synced up with the right lines as much as I'd like:

data

I want to duplicate the column A and D values down into any empty spaces/rows.

So in the end, all the spaces in those columns would become like this:

changed_data

Is there any way to automatically do that to those columns in general?

(An automatic way to delete the rows with blanks at the end would be really useful as well)

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

You can do this with a simple formula. Given the range shown in your example, insert a new column to the right of column A. In this new column B, write:

B136: =A136
B137: =if( isblank(A137) ; B136 ; A137 )

Duplicate the formula in B137 into the cells below it.

Let's unpack this:

  1. B136 looks to the left for its value (A136).
  2. B137 looks to the left for its value (A137), but if blank, uses the previous value (B136).
  3. B138 looks to the left for its value (A138), but if blank, uses the previous value (B137).
  4. … and so on.

Depending on your needs, you might be done at this point. Optionally, you can hide column A. Or you can copy column B, "paste values only" into column A, and delete column B.

You asked to do the identical operation with the data in column D, so the solution will be the same.

E136: =D136
E137: =if( isblank(D137) ; E136 ; D137 )
…

Please post your followup question about rows with blanks at the end as a separate question, and provide more detail. It's not clear what the problem is.

share|improve this answer
add comment

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.