Results 1 to 9 of 9

Thread: How to rearrange data vertical to horizontal
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post How to rearrange data vertical to horizontal

    How do I turn the top data set into one that looks like the lower data set? I've tried the paste special method, but that's going to put all the information in its own cells like I need. PLEASE HELP!!

    What I have:

    SMITH, MARK JOSEPH
    SMITHTECH
    124 6th St.
    MADEUP, TX 77777
    (555)123-4567
    LICENSE: 1234
    CONTINUING EDUCATION HOURS: 1.23

    What I need: All in one line, with each entry in its own cell. With other entries to follow below.

    Last First Company Address City, State Zip ........etc.
    Smith Mark SmithTech 124 6th St. MadeUp, TX 77777
    Last edited by ThirdGenAggie06; Mar 25th, 2010 at 11:40 AM.

  2. #2
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    Hi - paste special transpose should do this?

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    It works, but I have a list of 2,000 people and if I highlight all 2,000 and do a paste transpose, it puts them all in one row together. I can highlight each person's information individually and paste transpose it, but I'm trying to find a way to do it without having to copy-paste-transpose 2,000 times...

  4. #4
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    I see, - I think it will be a VBA job. Is the data like

    SMITH, MARK JOSEPH
    SMITHTECH
    124 6th St.
    MADEUP, TX 77777
    (555)123-4567
    LICENSE: 1234
    CONTINUING EDUCATION HOURS: 1.23
    SMITH, MARK JOSEPH
    SMITHTECH
    124 6th St.
    MADEUP, TX 77777
    (555)123-4567
    LICENSE: 1234
    CONTINUING EDUCATION HOURS: 1.23

    or

    SMITH, MARK JOSEPH SMITH, MARK JOSEPH
    SMITHTECH SMITHTECH
    124 6th St. 124 6th St.
    MADEUP, TX 77777 MADEUP, TX 77777
    (555)123-4567 (555)123-4567
    LICENSE: 1234 LICENSE: 1234
    CONTINUING EDUCATION HOURS: 1.23 CONTINUING EDUCATION

  5. #5
    New Member
    Join Date
    Mar 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    The data reads just like the second one you have there... there are 2,000 entries like that listed vertically down the spreadsheet with blank cells separating them from each other.

  6. #6
    New Member
    Join Date
    Mar 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    No, scratch that... it is listed like the first one, but it has a blank cell between the two entries...

    Joe Shmo
    XXX
    XXX
    XXX

    Jane Shmo
    XXX
    XXX
    XXX

  7. #7
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    Are there always 7 records for each person or can they differ?

    i.e. always
    Code:
     
    1  SMITH, MARK JOSEPH
    2  SMITHTECH
    3  124 6th St
    4  MADEUP, TX 77777
    5  (555)123-4567
    6  LICENSE: 1234
    7  CONTINUING EDUCATION HOURS: 1.23

  8. #8
    Board Regular Dannyh1's Avatar
    Join Date
    Nov 2009
    Location
    Liverpool UK
    Posts
    1,138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    Please bear in mind - I've only started to teach myself VBA in the last year so a proper VBA expert would probably despair - but this is how I would do it

    1. Insert a column before your info
    2. Number you 1st set of data 1-7 (I am now assuming numbers colmn A, info col B & all the data is on Sheet(1) )

    3. On Sheet(2) enter headers B2 to H2

    4. Run below macro

    Code:
    Sheets(1).Select
        Range("A3:A9").Select
        Selection.Copy
        
        Do While Selection.End(xlDown).Offset(2, 1) <> ""
        Selection.End(xlDown).Select
        Selection.Offset(2, 0).Select
        ActiveSheet.Paste
        Loop
        
        Columns("A:A").Select
        Selection.Replace What:="", Replacement:="blnk", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        
        Dim rng As Range
        Set rng = Sheets("sheet1").Range("B3:B20000").SpecialCells(xlCellTypeVisible)
        
        Range("A2").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="1"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("B3").Select
        ActiveSheet.Paste
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="2"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("C3").Select
        ActiveSheet.Paste
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="3"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("D3").Select
        ActiveSheet.Paste
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="4"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("E3").Select
        ActiveSheet.Paste
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="5"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("F3").Select
        ActiveSheet.Paste
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="6"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("G3").Select
        ActiveSheet.Paste
        
        
        Sheets(1).Select
        Range("A2").Select
        Selection.AutoFilter Field:=1, Criteria1:="7"
        rng.Select
        Selection.Copy
        Sheets(2).Select
        Range("H3").Select
        ActiveSheet.Paste

  9. #9
    New Member
    Join Date
    Feb 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to rearrange data vertical to horizontal

    How do I turn the top data set into one that looks like the lower data set? I've tried the paste special method, but that's going to put all the information in its own cells like I need. PLEASE HELP!!

    What I have:


    01
    02
    03
    04
    05
    06
    07


    WHAT I NEED

    01 02 03

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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