Excel - fill in blanks with next piece of data

[kb178]

An example will explain what i need to do best. This is what I have:
Field 1 Field2
100
102
101
103
---100--- ---100---
201
203
205
200
---200--- ---200---

I was able to move over the ---100--- and so on to another field. I'd like to fill everything blank above the number with the next piece of data, so I wind up with:

Field 1 Field2
100 ---100---
102 ---100---
101 ---100---
103 ---100---
---100--- ---100---
201 ---200---
203 ---200---
205 ---200---
200 ---200---
---200--- ---200---

Is this possible? Sure, I could just grab the bottom number, and fill upwards, but I have *a lot* of records. Thanks!

 

[SkipVought]

Hi,
Here's an approch using a fornula...

1. Assuming that your data is in columns A & B, in column C...

=IF(ISBLANK(B2),C3,B2)

2. Copy this formula down as far as you have data
3. select ALL THE FORMULAS in column C and COPY
4. select B2 and Paste Special - Values
5. delect column C

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

One simple way:-

Assuming your data (including blanks) is all in Col B in range B1:B1000. Select B1:B1000 and do Edit / Go To / Special / Blanks. What you will now see is all the blanks highlighted and the active cell is probably B1. Scroll down (using the mouse and the bars) to B999, hold down CTRL and click onto cell B999. Now type = and then holding CTRL down again hit enter.

When done simply select all data in Col B and copy and paste special as values.

Regards
Ken...............

 

[kb178]

Genius! Thank you!

 

[KenWright]

CORRECTION TO EARLIER POST!!

Last line of mine should have read:-

OLD TEXT:-
Now type = and then holding CTRL down again hit enter.

NEW TEXT:-
Now type = then click on B1000 and then holding CTRL down again hit enter.

Regards
Ken..................