Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Moving Data from a Web Browser to Excel | Excel Discussion (Misc queries) | |||
Moving data between Excel sheets | Excel Discussion (Misc queries) | |||
Moving blocks of data in excel | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) |