Import txt file with multiple rows for each record

[Guest]

I have a royalty report from SAP that I need to bring into Excel. It
includes check number, payment date, amount, and check recipient.

The problem is the "check recipient" information comes in on either 1,2,3,
or 4 lines in the report. But the rest of the record information is all on
line 1. This is random and will not be the EXACT same structure each month.

Ex.
2539893 3/14/2002 55.09 Jane Doe
Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith
111 Main Street
Anytown USA

I need the end result to look like
2539893 3/14/2002 55.09 Jane Doe Anytown USA
2539894 3/14/2002 79.99 Bob Evans
2539895 3/14/2002 5.00 John Smith 111 Main Street Anytown USA

I have very little VBA or Macro experience, but wiling to learn. Also,
could Access handle this better?

 

[Guest]

I can fix it in 10 minutes. Can you email it to me?

 

[Guest]

Sure, what is your email?
I also need to learn ~how~ to do this, I have to do this monthly. Will you
be able to explain how to do it for me?

 

[Guest]

Mike I sent it about 75 minutes ago.. did you get it? Any help is appreciated.

 

[Dave Peterson]

Try this against a copy of your worksheet--it destroys the original data as it
processes the data. (Or just close without saving if it's wrong.)

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim TopRow As Long
Dim iRow As Long
Dim dummyRng As Range
Dim rngToCopy As Range

Set wks = Worksheets("Sheet1")
With wks
FirstRow = 1
Set dummyRng = .UsedRange 'try to reset last used cell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

TopRow = FirstRow
For iRow = FirstRow To LastRow Step 1
If IsEmpty(.Cells(iRow, "A")) = False Then
TopRow = iRow
Else
If Application.CountA(.Rows(iRow)) > 0 Then
'found some stuff to move
Set rngToCopy = .Range(.Cells(iRow, "A").End(xlToRight), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
rngToCopy.Copy _
Destination:=.Cells(TopRow, .Columns.Count) _
.End(xlToLeft).Offset(0, 1)
End If
End If
Next iRow

On Error Resume Next
.Range("a1").EntireColumn.Cells.SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
End With
End Sub

It looks at column A. If that cell is filled in, it knows to use that to
receive the other row's info.

If column A is empty, then it finds the first column with data and the last
column with data and copies it after the rightmost cell of that row with
something in column A.

When it's done, it deletes all the rows that have column A empty.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm