Copy down a column

[Timoty]

I have a very large excel spreadsheet (60,000 lines). There are four columns. The First column is poplated and then lists a number of files in the three remaining colums. What I need to do is copy and paste the first colum so that it matches up with every line in the three remaining colums like below.

Tim c:\pictures apple 3000KB
c:\pictures orange 325KB
c:\pictures banana 4000KB


Tim c:\pictures apple 3000KB
Tim c:\pictures orange 325KB
Tim c:\pictures banana 4000KB

Some entries have only one file in them while others have over 30 files.

Rather than me sitting here and copying and pasting for the next two weeks, is there any formula that I can use to copy and paste the first entry until the cell in colum two is null etc.

 

[Timoty]

that didn't post the way I wanted it to. In the first list there is supposed to be blanks under the first tim representing nothing in the first column except for the first entry.

 

[Timoty]

Example

This is an example of what I need done. From A to B, without manually going down 60,000 lines and copy and pasting.

 

[mdjks]

=IF(B2="",A1,B2)
Insert a column before the first and use the formula above to fill. Make sure your blank cells are really blank and don't contain spaces.

 

[Timoty]

That did work (more or less). I can use it to make it work. The problem being that there or a number of spaces between entries. However, by incerting the formula and then importing into Access, I beleive I will save myself many hours of work. Thanks again.

 

[GolfProRM]

Here's a simple macro you could create that will accomplish what you want. Just record a new macro, then stop the macro before doing anything. Go to the Macros, and select "Edit" then paste the code into the macro. If your sheet is named something else, you'll have to change the sheet name.

    Worksheets("Sheet1").Activate
    For rwNumber = 1 To 65536
        If Worksheets("Sheet1").Cells(rwNumber, 1) = "" And Worksheets("Sheet1").Cells(rwNumber, 2) <> "" Then
            Worksheets("Sheet1").Cells(rwNumber, 1) = Worksheets("Sheet1").Cells(rwNumber - 1, 1)
        End If
    Next rwNumber

You can also adjust the ending row number, but there's no harm in leaving it long.

 

[Brianwarnock]

That did work (more or less). I can use it to make it work. The problem being that there or a number of spaces between entries. However, by incerting the formula and then importing into Access, I beleive I will save myself many hours of work. Thanks again.

Presumably you have ended up with 3 cols of data , col a has extra entries where cols b and c have spaces, sort the worksheet on col b this will group the excess rows together, delete them and resort on col a.

brian

 

[Timoty]

Thanks. You guys just saved me a weeks worth of mind numbing work. I should be done by the end of the day now. There is just over 390,000 lines of info on 22 sheets.