Thread: Creating and filling out columns automatically

Hey all!

I am sitting with a quiet large "logfile" I want to present...
But first I need to break it up into columns, and so far I have manually ctrl+x, ctrl+v everything, but I am about to go nuts from doing it... =)

Currently I have all my data in one column, and it looks like this (the number of rows under each "Logfile xxx" differs greatly...some have one row, others have 30+ rows):


Logfile xxx
1324657
1234567
1324657
Logfile xxx
1234567
1234567
1234567

and so on....


I want Excel to "cut out" each part starting at "Logfile xxx" all the way down to right before the next "Logfile xxx" and paste that into a new column.

So it will look like this basically:

Logfile xxx | Logfile xxx
1324657 | 1324657
1234567 | 1324657
1324657 | 1324657

and so on....

I have about 11k rows to do, so you might imagine my frustration =)

Any suggestions on how to do this?

Thanx alot!

wharfedale,

Welcome to the Excel Forum.

Please attach your workbook, or, attach a sample of your workbook/worksheet.

Click on "New Post", then scroll down and see "Manage Attachments".

Try this macro.

Code:

Sub MySplit()
CurrentColumn = 2
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Left(Cells(N, 1), 7) = "Logfile" Then
        CurrentColumn = CurrentColumn + 1
        Cells(1, CurrentColumn) = Cells(N, 1)
    Else
        Cells(Rows.Count, CurrentColumn).End(xlUp).Offset(1, 0) = Cells(N, 1)
    End If
Next N
End Sub

I'm assuming that your data is in column A and other columns are empty.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

Slight change needed.

Code:

Sub MySplit()
CurrentColumn = 2
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If InStr(Cells(N, 1), "Logfile") > 0 Then    
        CurrentColumn = CurrentColumn + 1
        Cells(1, CurrentColumn) = Cells(N, 1)
    Else
        Cells(Rows.Count, CurrentColumn).End(xlUp).Offset(1, 0) = Cells(N, 1)
    End If
Next N
End Sub

wharfedale,

With your raw data in Sheet1, Sheet2 will contain your results.

Detach/open the attached workbook "RearrangeData - Creating and filling out columns automatically - wharfedale - SDG11.xlsm" and run macro "RearrangeData".