Rows to Columns with Every Other Row Formula

[Debra Lucius]

Hello,

I'm working with a Word Document that includes course number, course title and course description. I saved the document as a .txt file and used Excel to open the .txt file.

The current Excel format is one column; one row contains course number and title and the row directly below contains course description. My goal is to add 3 additional columns to include course number, course title, and course description. Course description needs to included the information from Col B and insert description into column E. In the example below B1 data should appear in C1 and D1; B2 should appear in E1 without (Course Credits).

Is this possible? Thank you for your assistance

A	B	C	D	E
Row	Current Format	Course Number	Course Title	Course Description
1	ACT 5000 - Financial and Managerial Accounting	ACT 5000	Financial and Managerial Accounting	Financial statements, accrual accounting, statement of cash flows, financial reporting, cost behavior, cost-volume-profit analysis, activity based costing, profit planning, budgeting, flexible budgeting, segment profitability reporting, relevant cost data for decision making, transfer pricing, product and services pricing, and performance measuring including ROI, residual income, and balanced scorecard, including measures to analyze costs and factors associated with sustainability
2	(3 units) Financial statements, accrual accounting, statement of cash flows, financial reporting, cost behavior, cost-volume-profit analysis, activity based costing, profit planning, budgeting, flexible budgeting, segment profitability reporting, relevant cost data for decision making, transfer pricing, product and services pricing, and performance measuring including ROI, residual income, and balanced scorecard, including measures to analyze costs and factors associated with sustainability
3	ACT 6015 - Financial Reporting	ACT 6015	Financial Reporting	This course focuses on the principles and practices in the preparation and use of financial statements such as the balance sheet, income statement, the statement of cash flows and other documents for financial reporting. Significance and limitations of financial statements are studied to allow students making informed decisions based on the provided financial documents.
4	(3 units) This course focuses on the principles and practices in the preparation and use of financial statements such as the balance sheet, income statement, the statement of cash flows and other documents for financial reporting. Significance and limitations of financial statements are studied to allow students making informed decisions based on the provided financial documents.

<tbody>
</tbody>

 

[MichaelS94]

Play around with the data import wizard for text files.

Go to Data tab > Get External Data > From Text (this opens up the wizard)

You then want to selected the "Delimited" option - this determines what Excel will look for to split one cell into two. You would go "other" and enter a hyphen. As you should see, it will split ACT 5000 - Financial and Managerial Accounting into two columns "ACT 5000" and "Financial and Managerial Accounting".

However, the description will still be in the row below. I'm not sure that there is a way to automatically and easily solve this using a data import method, although there are many workaroudns to move it to the 3rd column

 

[MickG]

Try this :-
Nb:- Adjust column widths to suit !!!

[COLOR="Navy"]Sub[/COLOR] MG21Dec09
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
Lst = Range("B" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]With[/COLOR] Range("C:E")
    .WrapText = True
    .VerticalAlignment = xlTop
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] Rw = 2 To Lst [COLOR="Navy"]Step[/COLOR] 2
    [COLOR="Navy"]With[/COLOR] Range("B" & Rw)
        Sp = Split(.Value, "-")
        .Offset(, 1).Value = Sp(0)
        .Offset(, 2).Value = Sp(1)
        .Offset(, 3).Value = Split(.Offset(1).Value, ")")(1)
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Debra Lucius]

Hi Mick,

Thank you for your assistance.
I copied and paste the VB code but when I ran the macro but ran into a problem with Sub MG21Dec09().

 

[MichaelS94]

Debra,

I recommend trying Mick's solution (as he seems far more experienced at VBA), however I am trying to learn and have come up with an alternative slightly rusty solution, but one which works for me. In this, I assume that when you import the data, you have 500 rows. If there are fewer than 500, then it will finish with an error (but will still work), if more than 500, then you will need to change this in the code.

Sub Macro1()

Dim Rowcounter As Integer

For Rowcounter = 1 To 500
    Cells(Rowcounter, 1).Select
    Selection.TextToColumns Destination:=Cells(Rowcounter, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="-"
    Cells(Rowcounter + 1, 1).Select
    RemoveText = Mid(Selection, 11)
    Cells(Rowcounter, 3).Value = RemoveText
    
    Rows(Rowcounter + 1).Delete
    
Next Rowcounter

End Sub

For more experienced coders who may see this - I'd appreciate your feedback on my code - as I said, I'm trying to learn! Thanks!

 

[MichaelS94]

**Forgot you had headers in the first row** please try this instead:

Sub Macro1()

Dim Rowcounter As Integer

For Rowcounter = 2 To 500
    Cells(Rowcounter, 1).Select
    Selection.TextToColumns Destination:=Cells(Rowcounter, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, Other:=True, OtherChar _
        :="-"
    Cells(Rowcounter + 1, 1).Select
    RemoveText = Mid(Selection, 11)
    Cells(Rowcounter, 3).Value = RemoveText
    
    Rows(Rowcounter + 1).Delete
    
Next Rowcounter

End Sub

 

[Debra Lucius]

MichaelS94,

Is it possible to retain the original values in Col A? I noticed missing words for course description.

Thank you for your assistance

 

[MichaelS94]

That might be a result of the code

RemoveText = Mid(Selection, 11)

This was an attempt to remove the course credits "(3 units)" at the start of the course description. In my logic, that is 10 characters, and so of the text in A2 which you want moved to C1 (course description), you want the copied text to start at the 11th character (ignoring the first 10 characters).

Do all of the course descriptions have course credits at the start?

 

[Debra Lucius]

Yes all course descriptions have course credits. I see a few courses where course credits equals "(1.5 units)".

 

[MickG]

If you paste the code into a basic module then it should run when you select the code from the macro dialog box and click "Run"
If you pasted into a CommandButton , you will need to remove that line starting "Sub"