Splitting Out Rows
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Splitting Out Rows

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

    Default

     
    Let's say I have a large group of data like this:

    12:00 AM 4
    12:00 AM 7
    12:00 AM 34
    12:00 AM 3
    12:30 AM 31
    12:30 AM 4
    12:30 AM 4
    12:30 AM 7

    with multiple values for each time. How can i get the data to look like this:

    12:00 AM 4 12:00 AM 7 12:00 AM 34, etc?

    In other words, I want the large column of data split into eight columns whereby columns 1,3,5,7 would be the dates and 2,4,6,8 would be the data. Any ideas?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    12:00 AM 4
    12:00 AM 7
    12:00 AM 34
    12:00 AM 3
    12:30 AM 31
    12:30 AM 4
    12:30 AM 4
    12:30 AM 7



    12:00 AM 4 12:00 AM 7 12:00 AM 34, etc?

    In other words, I want the large column of data split into eight columns whereby columns 1,3,5,7 would be the dates and 2,4,6,8 would be the data.

    >> Sorry cant see a dte in your example but try right and left to pick off the cells you require. combine with FIND BLANK and from left will return as you want.. OK different formula in each cell but once done can DRAG..

    any ggod.

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi StrangeLuck,

    Here is a simple macro (with a helper procedure) that does this. I assumed that the data you showed in your example was all in one column, not two. This code is based on the data starting in column A, row 1, but hopefully it is obvious how to modify it to work with any other column or starting row.

    Sub RowsToColumns()
    Dim iRow As Long
    Dim LeftPart As String
    Dim RightPart As String
    For iRow = 1 To [a65536].End(xlUp).Row
    ParseString Cells(iRow, 1), LeftPart, RightPart
    Cells(iRow, 1) = ""
    Cells(1, 2 * (iRow - 1) + 1) = LeftPart
    Cells(1, 2 * iRow) = RightPart
    Next iRow
    End Sub

    Sub ParseString(StrIn As String, Token1 As String, Token2 As String)
    'Parses string, separating values using last blank separator in
    'string to divide the two tokens
    Dim Schr As Integer 'the position of the last blank
    Dim iCh As Integer

    For iCh = Len(StrIn) To 4 Step -1
    If Mid(StrIn, iCh, 1) = " " Then
    'token separating blank found at character iCh
    Token1 = Left(StrIn, iCh - 1)
    Token2 = Mid(StrIn, iCh + 1)
    Exit Sub
    End If
    Next iCh
    'No token2 found
    Token1 = StrIn
    Token2 = ""
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

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
  •  

 

 
DMCA.com