ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   convert txt file to excel (https://www.excelbanter.com/excel-discussion-misc-queries/197014-convert-txt-file-excel.html)

accessnewbie
convert txt file to excel
 
I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

joel
convert txt file to excel
 
Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

accessnewbie
convert txt file to excel
 
Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

joel
convert txt file to excel
 
Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

accessnewbie
convert txt file to excel
 
Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

joel
convert txt file to excel
 
I'm a litt confused by you cell references. Can you show the before and
after results.

"AccessNewbie" wrote:

Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

joel
convert txt file to excel
 
I read your postings again and I think this should work

Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
ColCount = 2
NextRowColCount = 1
For LoopCount = 1 To LastCol
Cells(RowCount, ColCount).Insert (xlShiftToRight)
Cells(RowCount + 1, NextRowColCount).Copy _
Destination:=Cells(RowCount, ColCount)

ColCount = ColCount + 2
NextRowColCount = NextRowColCount + 1
Next LoopCount
Rows(RowCount + 1).Delete
End If
Next RowCount
End Sub



"AccessNewbie" wrote:

Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

accessnewbie
convert txt file to excel
 
Joel,

you are the best. thank you so much. It's great and it works perfect!
Thanks AGAIN!!!

"Joel" wrote:

I read your postings again and I think this should work

Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
ColCount = 2
NextRowColCount = 1
For LoopCount = 1 To LastCol
Cells(RowCount, ColCount).Insert (xlShiftToRight)
Cells(RowCount + 1, NextRowColCount).Copy _
Destination:=Cells(RowCount, ColCount)

ColCount = ColCount + 2
NextRowColCount = NextRowColCount + 1
Next LoopCount
Rows(RowCount + 1).Delete
End If
Next RowCount
End Sub



"AccessNewbie" wrote:

Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

joel
convert txt file to excel
 
It turns out the previous code I sent it better than the one that worked.
All yo uhad to do was to move the column at the end. The code that works is
slow because of all the cell insertions.

"AccessNewbie" wrote:

Joel,

you are the best. thank you so much. It's great and it works perfect!
Thanks AGAIN!!!

"Joel" wrote:

I read your postings again and I think this should work

Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
ColCount = 2
NextRowColCount = 1
For LoopCount = 1 To LastCol
Cells(RowCount, ColCount).Insert (xlShiftToRight)
Cells(RowCount + 1, NextRowColCount).Copy _
Destination:=Cells(RowCount, ColCount)

ColCount = ColCount + 2
NextRowColCount = NextRowColCount + 1
Next LoopCount
Rows(RowCount + 1).Delete
End If
Next RowCount
End Sub



"AccessNewbie" wrote:

Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.

accessnewbie
convert txt file to excel
 
Joel,

It's runs great and I'm very happy with it. Thanks again. Can you
recommend any VBA books, classes or anything that will help me to program? I
am so grateful for the help I can get from this group but I'd also like to
continue learning to program. Again, thanks!!!

"Joel" wrote:

It turns out the previous code I sent it better than the one that worked.
All yo uhad to do was to move the column at the end. The code that works is
slow because of all the cell insertions.

"AccessNewbie" wrote:

Joel,

you are the best. thank you so much. It's great and it works perfect!
Thanks AGAIN!!!

"Joel" wrote:

I read your postings again and I think this should work

Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
ColCount = 2
NextRowColCount = 1
For LoopCount = 1 To LastCol
Cells(RowCount, ColCount).Insert (xlShiftToRight)
Cells(RowCount + 1, NextRowColCount).Copy _
Destination:=Cells(RowCount, ColCount)

ColCount = ColCount + 2
NextRowColCount = NextRowColCount + 1
Next LoopCount
Rows(RowCount + 1).Delete
End If
Next RowCount
End Sub



"AccessNewbie" wrote:

Joel,
Thanks for the code. It did a wonderful job of placing the entire row at
the end of the first row. However, the organization is not in the right
order. Somehow during the extraction the two lines were offset in different
order. Here is what the first two lines of another report (they were all
extracted the same way) look like using cell references from where the
information should come from. Is there any way to write the code so it
"counts", left 2, down 2, etc., and put it in a loop so it does this until it
finds the blank A cell?

31000 Week Ending Date 11/1/1997
Func Code Prog Code Func Title Prog Title Period Vol Cum Vol
a4 a5 b4 b5 d4 d5
a6 a7 b6 b7 d6 d7

32000

"Joel" wrote:

Because of the blank rows and the team name row not being combined I had to
make some minor changes to my original code.



Sub CombineRows()

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

For RowCount = 1 To LastRow
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
If LastCol.Column 1 Then
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete
End If
Next RowCount

End Sub

"AccessNewbie" wrote:

Joel,

Thanks for the help. Here are additional lines:


Team1
Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2 Expended2 Hours Complete2 Work2 Defect2
Jim 3172 4000 No Yes
22 2222 29.7 No

TEAM TEAM TIME 4976.395 14
TOTALS CUMULATIVE 1340809 47

Team2

Driver1 Time1 Hrs1 Balance1 Exception1
Car1a Expended1 Hours Complete1 Work1 Defect1
Marc 371 85 Yes Yes
79 1497 19 Yes

Driver2 Time2 Hrs2 Balance2 Exception2
Car2a Expended2 Hours Complete2 Work2 Defect2
BJW 372 59 No Yes
63 112 14 Yes

TEAM2 TEAM TIME2 1476.1 393
TOTALS2 CUMULATIVE2 7576.2 4.3

Again, thanks for any ideas.


"Joel" wrote:

Can you show a few more lines of data. I need to know the spacing between
input lines to get the right solution. Is ther any blank rows between the
line of data?

I think this may work

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) < ""
Set LastCol = _
Cells(RowCount, Columns.Count).End(xlToLeft)
Set NewCol = LastCol.Offset(0, 1)
Set NextRowLastCol = _
Cells(RowCount + 1, Columns.Count).End(xlToLeft)
Range(Range("A" & (RowCount + 1)), NextRowLastCol).Copy _
Destination:=NewCol
Rows(RowCount + 1).Delete

RowCount = RowCount + 1
Loop

End Sub


"AccessNewbie" wrote:

I have almost 100 very old text reports that must be converted to excel. My
problem is the reports have been changed (no other way to export them) to run
two separate lines and I need them back to the single line. Ex:

Driver1 Time Hrs Balance Exception
Car Expended Hours Complete Work Defect
Eric 4 3.5 Yes No
15 4 .5 Yes

These lines need to be:
Driver1 Car Time Expended Hrs Hours Complete Balance
Exception Work Defect
Eric 15 4 4 3.5 .5
Yes Yes No

I'm a novice vba/macro person and welcome any suggestions.


All times are GMT +1. The time now is 06:05 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com