Moving data in Excel 2003
 

I have a data file imported from a AS400 down load and it is not coming in
neatly.
All the even rows, starting at row 2, use columns A.B,C,D,and E.
Odd rows use column A
We are using several hundred rows.
How can I move the data from each odd row to Column F in the row immediately
above.
THis is an on going event and the number of rows may vary from one week to
another.

Thank you

Moving data in Excel 2003
 

A little more general than you indicated, but less prone
to errors in that it does not check for even/odd rows,
and can be rerun without destroying data.

Starting from the bottom, because that is what you do when
inserting or deleting rows so you don't stumble all over yourself..

If there is a value in A, nothing else in the row, and no value in F of the row above
then the cell in A will be placed in column F of the row above
and the row will be deleted.

Sub Join_Overflowed_A()
'-- Merge cells in A w/o other values on Row to row above w/o F
' D.McRitchie, 2008-02-23 in excel.newusers
Dim LastRow As Long
Dim I As Long
LastRow = Cells.SpecialCells(xlLastCell).Row
For I = LastRow To 2 Step -1
If Not IsEmpty(Cells(I, 1)) And Cells(I - 1, 6) = "" _
And Application.CountA(Cells(I, 1).EntireRow) = 1 Then
Cells(I - 1, 6) = Cells(I, 1).Formula
Cells(I, 1).EntireRow.Delete
End If
Next I
done:
End Sub
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"PA" wrote in message ...
I have a data file imported from a AS400 down load and it is not coming in
neatly.
All the even rows, starting at row 2, use columns A.B,C,D,and E.
Odd rows use column A
We are using several hundred rows.
How can I move the data from each odd row to Column F in the row immediately
above.
THis is an on going event and the number of rows may vary from one week to
another.

Thank you

Moving data in Excel 2003
 

Thank you David, I will give this a try upon returning to the office tomorrow.

"David McRitchie" wrote:

A little more general than you indicated, but less prone
to errors in that it does not check for even/odd rows,
and can be rerun without destroying data.

Starting from the bottom, because that is what you do when
inserting or deleting rows so you don't stumble all over yourself..

If there is a value in A, nothing else in the row, and no value in F of the row above
then the cell in A will be placed in column F of the row above
and the row will be deleted.

Sub Join_Overflowed_A()
'-- Merge cells in A w/o other values on Row to row above w/o F
' D.McRitchie, 2008-02-23 in excel.newusers
Dim LastRow As Long
Dim I As Long
LastRow = Cells.SpecialCells(xlLastCell).Row
For I = LastRow To 2 Step -1
If Not IsEmpty(Cells(I, 1)) And Cells(I - 1, 6) = "" _
And Application.CountA(Cells(I, 1).EntireRow) = 1 Then
Cells(I - 1, 6) = Cells(I, 1).Formula
Cells(I, 1).EntireRow.Delete
End If
Next I
done:
End Sub
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"PA" wrote in message ...
I have a data file imported from a AS400 down load and it is not coming in
neatly.
All the even rows, starting at row 2, use columns A.B,C,D,and E.
Odd rows use column A
We are using several hundred rows.
How can I move the data from each odd row to Column F in the row immediately
above.
THis is an on going event and the number of rows may vary from one week to
another.

Thank you

Moving data in Excel 2003
 

Is Row 1 empty, or does it have something in A1?

Assuming A1 is empty, I, being the lazy sort, would make G2 through K2 be
equal to A2 through E2. Then I'd make L2 be equal to A3. Then I'd copy
G2:L3 down. That'd leave you with the data set up the way you want, only
double spaced. I'd copy the values, using Paste Special to a new sheet and
sort it leaving the empty rows at the bottom.

Or better, would the folks on the AS400 side tweak their query for you?
Maybe they could just increase the line length and solve your problem.









"PA" wrote:

I have a data file imported from a AS400 down load and it is not coming in
neatly.
All the even rows, starting at row 2, use columns A.B,C,D,and E.
Odd rows use column A
We are using several hundred rows.
How can I move the data from each odd row to Column F in the row immediately
above.
THis is an on going event and the number of rows may vary from one week to
another.

Thank you