Fill the below blank cells

[vinu]

Hi,

I just copied an pivot data and pasted it in different Sheet.
I needed to fill the cells that was blank with the data above each set
of blank cells..

Day VendorName VehicleType VehicleNo StartTime
1 ATL B 1246 18:00
22:38
1558 12:30
1815 4:27
6:00
7:00
7:30
12:55
7915 16:00
20:30
22:30
8037 1:00
5:30
7:00
9583 0:00
3:45

I can solve this by dragging the data down but I have do it at least
4000 times...

Is there any formula to this...?!

Thanks in advance
Vinod

 

[Pete_UK]

Highlight the columns A to E by clicking on the column identifiers.
Then press F5 (GoTo) and click Special, then click on Current Region.
Press F5 again, click Special then click on Blanks. You should have
all the blank cells highlighted, with A3 as the active cell. Begin to
enter a formula by typing =, and then click on cell A2, and then do
CTRL-Enter and all those blank cells will be filled with a formula
which takes the value from the cell above.

You could fix the values by clicking <copy>, then right-click and
Paste Special | Values (check) | OK then <Esc>.

Hope this helps.

Pete

 

[Dave Peterson]

Debra Dalgleish shares a few techniques here (manual and macro):

http://contextures.com/xlDataEntry02.html
and a video
http://www.contextures.com/xlVideos01.html#FillBlanks

 

[vinu]

Hi Pete,

Thanks a lot for your help. It's working fine with my data.

There is one more problem I' facing in the same data, When I try
subtract the starting time from the end time the outcome will be like
###### in some cells where the end time was >= "0:00 am".

Any help....

Regards,

Vinod

 

[Rick Rothstein]

What happens if you widen the column?

 

[vinu]

It's just expanding to more #'s.....

I think it's because after 23:59 it will be a next day and that was
leading to this kind of issue.

Pls help me..

Vinod

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

As Rick says, you could widen the column and see what happens.

I suspect, though, that this might be caused if you are trying to
subtract a start time (like 18:00) from a finish time which is
numerically smaller (like 06:00), where the finish time has rolled
past midnight and is on another day. You don't indicate if you have
the date corresponding to the start or finish times, so all you can do
is to add 1 onto the result which will assume a rollover to the next
day.

Hope this helps.

Pete

 

[vinu]

Thanks to Pete/Dave/Rick...

You helped me to save at least few hours on my hectic work schedule.

Regards,
Vinod

 

[Pete_UK]

You're welcome - thanks for finding the time to feed back..

Pete