Shifting Data (XL 03)

Topic

New Reply

I have a system generated report that I have imported into XL. This report has part numbers with data found on 2 different rows that I would liked move to one single row with specific headings, so that I can filter and pivot if need be.

Attached is the file, you will see Columns J, K, and L are color coded to their respective information on row 5. Basically I need D5 to move to J4, F5 to K4, and I5 to L4.

I will then delete the blank lines and have a workable data set in which to manipulate. Is there an easy way to do this, as I have only attached a file showing 54 lines of this 3000 line report.

Reply | Quote

Replies

See if this will do what you want:


Public Sub MoveToOneLine()
Dim I As Long
For I = Range("A65536").End(xlUp).Row - 1 To 3 Step -2
Range("J1").Offset(I, 0).Value = Range("D1").Offset(I + 1, 0).Value
Range("K1").Offset(I, 0).Value = Range("F1").Offset(I + 1, 0).Value
Range("L1").Offset(I, 0).Value = Range("I1").Offset(I + 1, 0).Value
Range("A1").Offset(I + 1, 0).EntireRow.Delete
Next I
End Sub


Reply | Quote

Legare,

Thank you very much, this has saved me a ton of time and has given me a more reason to crunch more numbers.

Thanks a Million

Reply | Quote