question on re-formatting a report

[pi567]

How can I get a report that's copied to Excel in this format
(originally an .rtf file):

Division A
Accounting
Invoice#
123 $100
567 $345

Division A
Marketing
Invoice#
456 $50
877 $2000

to look like this:

Division A Accounting 123 $100
Division A Accounting 567 $345
Division A Marketing 456 $50
Division A Marketing 877 $2000

It's a massive report that can go up to 200 pages. Any assistance is
greatly greatly appreciated.

Thanks
Nina

 

[Lori]

Try this:

- Copy the Data to columns D and E of a new sheet.
- Copy column D to columns B and C as well
- Select Cols A and B, press: [ctrl + \] [ctrl+click cell A1] [ctrl + D]
- Select Cols B and C, press: [ctrl + \] [ctrl+click cell A1] [ctrl + D]
- Select Col E, press F5>Special>Blanks. Edit>Delete>Entire Row

The VBA equivalent, with data in Sheet1!A:B of a new workbook, is:

Sub Reformat()
Selection.Copy Sheets("Sheet2").Range("D1")
Sheets("Sheet2").Select
Columns("D").Copy Columns("B:C")
Union(Columns("A:B").RowDifferences(Range("A1")), Range("A1")).FillDown
Union(Columns("B:C").RowDifferences(Range("B1")), Range("A1")).FillDown
Columns("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:A").Delete Shift:=xlToLeft
End Sub