VBA or formula solution to shift up and transform data for multiple ranges on a sheet

[gracemcth]

Any advice would be GREATLY APPRECIATED!!

I'm struggling to figure out a way to re-organize the data from a generated report into a more usable format. I have been able to get the data from the generated report to look like it does in the "before" columns, but I still need it to be shifted up [to the top row of each order] and transformed [the 3 amounts in the consult cost column transformed into the Consult Cost/Labor Cost/Material Cost columns] into how it looks in the "after" columns. This would be for a sheet with thousands of orders each with a varying number of rows.

THANK YOU AGAIN!!!

e

 

[Legacy 456155]

Try:

Sub Example()
    Dim r As Range
 
    For Each r In Range("A2:A" & Rows.CountLarge).SpecialCells(xlCellTypeConstants)
        Range(r.Offset(, 4).End(xlDown), r.Offset(, 4).End(xlDown).End(xlToRight).End(xlDown)).Cut r.Offset(, 4)
        r.Offset(, 8).End(xlDown).Resize(3).Copy
        r.Offset(, 8).PasteSpecial Transpose:=True
        r.Offset(, 8).End(xlDown).Resize(3).Clear
    Next
End Sub

Better yet:

Sub Example()
    Dim r As Range
   
    Application.ScreenUpdating = False
    For Each r In Range("A2:A" & Rows.CountLarge).SpecialCells(xlCellTypeConstants)
        With r.Offset(, 4)
            Range(.End(xlDown), .End(xlDown).End(xlToRight).End(xlDown)).Cut .Cells(1)
        End With
        With r.Offset(, 8)
            .End(xlDown).Resize(3).Copy
            .PasteSpecial Transpose:=True
            .End(xlDown).Resize(3).Clear
        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[gracemcth]

Wow! Thank you so much for your ideas. This is a great starting point. Here's what I'm running into with the second code (and this could definitely be user error...lol). I'm wondering if I should insert a blank row in between each order and then do some type of "ctrl+shift+right" and "ctrl+shift+down" to select the data for that order, shift/transform it, then somehow tell the code to go to the row after the blank row deliminating each order and loop back to the "ctrl+shift+right".......?

 

[Legacy 456155]

Grace, if possible, please drop a copy of your file here, noting the file name, or share it in some other way. I did not take the time to duplicate your file exactly and of course I don't know what might be in some of those apparently blank cells. There is an example in the folder linked to above named Grace 1.xlsm. Maybe you can work with that.

 

[alansidman]

Grace, we are unable to manipulate data in a picture. Please upload your sample data using XL2BB so that all here can have a look and try and help.