Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Import txt file with multiple rows for each record
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? |
#2
|
|||
|
|||
I can fix it in 10 minutes. Can you email it to me?
"VanessaNY" wrote: 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? |
#3
|
|||
|
|||
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? :-) "Mike" wrote: I can fix it in 10 minutes. Can you email it to me? "VanessaNY" wrote: 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? |
#4
|
|||
|
|||
Mike I sent it about 75 minutes ago.. did you get it? Any help is appreciated.
"VanessaNY" wrote: 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? :-) "Mike" wrote: I can fix it in 10 minutes. Can you email it to me? "VanessaNY" wrote: 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? |
#5
|
|||
|
|||
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(xlCel lTypeBlanks) _ .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 VanessaNY wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup multiple text rows | Excel Discussion (Misc queries) | |||
looking for a calendar template that I can import multiple dates | Excel Discussion (Misc queries) | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel |