automate filling in data

[bigdan]

I have a file here 17K lines long. Yikes!

Now each line needs a store number. Some of them are missing.

The fields I have are Store#, Doc#, and Amount. If a store # is missing from a row, which it often is, I'm seeing frequently the doc# is the same as the one above, which means this is the same store as the one above. But I cant do this a thousand times. Is there some way to automate this?

 

[dragontooth]

If you want to just delete the rows use autofilter. Selet the blank ones and delete all at once.

 

[Tom Urtis]

Assuming your Doc # is column B, select from cell B1 to the last used row, maybe cell B17000. It is better to do that than just click the column B header...resist that temptation and select the *cells* from B1:B17000.

Then hit the F5 key.

Click Special > Select Blanks > OK

Note which cell is above the first empty cell. Maybe the first empty cell is B8 so you will note that B7 is above that cell. Type this formula:
=B7
and apply it with Ctrl+Enter.

Then, select all of column B (OK to do that now), and hit Ctrl+C

Right-click anywhere in column B, select Paste Special > Values > OK.

Here is a link that pictorially describes the process using column A.

http://www.atlaspm.com/toms-tutoria...excel-fill-empty-cells-with-value-from-above/

 

[Sahak]

Hi bigdan

Try this:

Sub Fill_Missing()
    Dim Miss As Range
    For Each Miss In Range("A2:A17001")
        If Miss.Value = "" Then Miss.Value = Miss.Offset(-1, 0).Value
    Next
End Sub

 

[bigdan]

thanks a lot for the visual that really helped. and for the code as well sahak.

i think i may have been a bit misleading tho - the relevant data is not necessarily one row above. in some cases i've seen it a row below. so just copying the value from above isnt an option. especially since in some cases the relevant information is just not given.

Example:

Store Doc#
[blank] 553522
[blank] 561722 (store# can be copied from below)
2525 561722
[blank] 561722 (can be taken from above)
1575 553555
[blank] 553555 (can be taken from above)
[blank] 553555 (can be taken from above)
[blank] 665478


EDIT - the spacing here didnt really work out too well. is there some way to do that? i tried the indent, but that indented the whole line rather than just the second part of the line that i wanted to.