Results 1 to 8 of 8

Thread: How do you "re-organize" data?

  1. #1
    Neophyte msorange's Avatar
    Join Date
    Feb 2015
    Location
    Columbus, OH
    Posts
    4
    Articles
    0

    Question How do you "re-organize" data?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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 by Bob Phillips; 2015-02-06 at 12:24 AM.

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    805
    Articles
    0
    Excel Version
    Excel 2010
    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

  4. #4
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    Also with VBA macro.
    With location independent Tables.
    http://www.mediafire.com/download/zy.../02_05_15.xlsm

  5. #5
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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 C22 down as far as you require (until you see 0 appearing in the cells)
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  6. #6
    Neophyte msorange's Avatar
    Join Date
    Feb 2015
    Location
    Columbus, OH
    Posts
    4
    Articles
    0
    Thank you everybody! I am not well versed in VB so I used the formula.

  7. #7
    Neophyte msorange's Avatar
    Join Date
    Feb 2015
    Location
    Columbus, OH
    Posts
    4
    Articles
    0

    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!
    Attached Files Attached Files

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    805
    Articles
    0
    Excel Version
    Excel 2010
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •