How do you "re-organize" data?

M

msorange

New member
Joined
Feb 5, 2015
Messages
4
Reaction score
0
Points
0
Location
Columbus, OH
Hi,

I have a worksheet of data that is as follows:

Jones
purple
ohio
[blank row]
Smith
blue
california
[blank row]
Jackson
yellow
maine
[blank row]

And I would like the data to be like this.....

name Color Place
Jones purple ohio
Smith blue california
Jackson yellow maine


for each group I can copy, paste transposed. There must be a faster way. That would be over 800 "copy and paste"s!

Any and all help would be much appreciated. Thank you! :confused2:
 
Code: 
Public Sub ChangeLayout()Dim lastrow As Long
Dim i As Long


    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow - 2 To 1 Step -4
        
            .Cells(i + 2, "A").Copy .Cells(i, "C")
            .Cells(i + 1, "A").Copy .Cells(i, "B")
            .Rows(i + 1).Resize(2).Delete
        Next i
    
        .Rows(1).Insert
        .Range("A1:C1").Value = Array("Name", "Color", "Place")
    End With
    
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Maybe this, assumes your data starts in A2, reorganizes to start in B2, then you can delete column A.
Code: 
Sub ReOrganize()
    Dim lastrow As Long
    Dim PasteRow As Long
    Dim i As Integer
    
Application.ScreenUpdating = False

With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    PasteRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1

    For i = 2 To lastrow Step 4
        .Cells(i, 1).Resize(3, 1).Copy
        .Cells(PasteRow, 2).PasteSpecial Paste:=xlPasteAll, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        PasteRow = PasteRow + 1
    Next i

    Application.CutCopyMode = False
    .Range("A1").Activate
End With

Application.ScreenUpdating = True

End Sub
 
Hi

Assuming your data is in column A,with Jones in A2
In cell C2 enter
=INDEX($A:$A,(ROW(A1)-1)*4+2+COLUMN(A1)-1)
and copy across through D2:E2, and then copy C2:D2 down as far as you require (until you see 0 appearing in the cells)
 
still need help.......

I still need help! The formula worked, but I would have to do that for each section of data (same as copy and paste). I tried to do something with VB but I have no clue what I am doing. I have attached the spreadsheet here for someone to please look at it. The Find a grave website, green Lawn Cemetery, Columbus, OH is the source of my information if you prefer to go to the source and not open my file. This is getting me so frustrated. Thanks for helping!
 

Attachments

  • Find a grave_green lawn_20150205.xlsx
    93.4 KB · Views: 7
Have a look at this. It adds a new sheet for the re-organized data.
You will notice 2 persons where the b:date and d:date were omitted but you need to have something in there to maintain consistency.
 

Attachments

  • Find a grave_green lawn_20150205.xlsm
    106.7 KB · Views: 9
You must log in or register to reply here.
Back
Top