split 1 column into multiple columns
Results 1 to 8 of 8

Thread: split 1 column into multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default split 1 column into multiple columns

    How can I to split data in WS Sheet Column 'D' into Columns 'C' thru last column
    and place it under the right date. For example the Template sheet would have all the
    team schedules under the date they play, the court they play on and the time.

    A snippet of my macro thus far is shown at the end. It creates the data in the template sheet as shown in the example. But, I am lost on an approach to move the team data from the WS sheet under the matching date column along with getting the team data in the correct time and court place.





    WS Sheet example looks like this
    Col A Col B Col C Col D
    Date Time Courts Teams
    05/07/2019 10:00 AM 1 1 v 9
    05/07/2019 10:00 AM 2 3 v 5
    05/07/2019 10:00 AM 4 8 v 10
    05/07/2019 10:00 AM 5 7 v 6
    05/07/2019 10:00 AM 6 11 v 4
    05/14/2019 10:00 AM 1 7 v 5
    05/14/2019 10:00 AM 2 6 v 10
    05/14/2019 10:00 AM 3 2 v 9
    05/14/2019 10:00 AM 4 8 v 4
    05/14/2019 10:00 AM 5 1 v 11

    Template sheet example looks like this
    Col A Col B Col C Col D Col E
    05/07/2019 05/14/2019 05/21/2019
    Time Court
    10:00 1
    2
    3
    4
    5
    6
    7:00 1
    2
    3
    4
    5
    6






    Sub Mrexcel()


    Dim cl As Range
    Dim s1 As Integer
    Dim s2 As Integer
    Dim s3 As Integer
    Dim s4 As Integer

    'Copy unique dates from WS Sheet to Template sheet & convert them to rows
    Sheets("WS").Activate
    With CreateObject("scripting.dictionary")
    For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Item(cl.Value) = Empty
    Next cl
    Sheets("Template").Range("c3").Resize(, .Count).Value2 = .keys
    End With

    'Move Date & Time From WS Sheet to Template Sheet
    s2 = 5
    s4 = 5
    For s1 = 1 To NumofTimes
    Sheets("template").Range("A" & s2) = Sheets("WS").Range("F" & s1 + 1)
    For s3 = 1 To NumofCourts
    Sheets("template").Range("B" & s4) = Sheets("WS").Range("G" & s3 + 1)
    s4 = s4 + 1
    Next s3
    s2 = 5 + NumofCourts
    Next s1






    End Sub

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: split 1 column into multiple columns

    something like this?

    Date Time Courts Teams Time Courts 07/05/2019 14/05/2019
    07/05/2019
    10:00:00
    1
    1 v 9
    10:00:00
    1
    1 v 9 7 v 5
    07/05/2019
    10:00:00
    2
    3 v 5
    10:00:00
    2
    3 v 5 6 v 10
    07/05/2019
    10:00:00
    4
    8 v 10
    10:00:00
    3
    2 v 9
    07/05/2019
    10:00:00
    5
    7 v 6
    10:00:00
    4
    8 v 10 8 v 4
    07/05/2019
    10:00:00
    6
    11 v 4
    10:00:00
    5
    7 v 6 1 v 11
    14/05/2019
    10:00:00
    1
    7 v 5
    10:00:00
    6
    11 v 4
    14/05/2019
    10:00:00
    2
    6 v 10
    14/05/2019
    10:00:00
    3
    2 v 9
    14/05/2019
    10:00:00
    4
    8 v 4
    14/05/2019
    10:00:00
    5
    1 v 11


    btw. your result example has nothing to do with your source example
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: split 1 column into multiple columns

    Quote Originally Posted by sandy666 View Post
    something like this?

    [COLOR=#FFFFFF ]Date[/COLOR] [COLOR=#FFFFFF ]Time[/COLOR] [COLOR=#FFFFFF ]Courts[/COLOR] [COLOR=#FFFFFF ]Teams[/COLOR] [COLOR=#FFFFFF ]Time[/COLOR] [COLOR=#FFFFFF ]Courts[/COLOR] [COLOR=#FFFFFF ]07/05/2019[/COLOR] [COLOR=#FFFFFF ]14/05/2019[/COLOR]
    07/05/2019
    10:00:00
    1
    1 v 9
    10:00:00
    1
    1 v 9 7 v 5
    07/05/2019
    10:00:00
    2
    3 v 5
    10:00:00
    2
    3 v 5 6 v 10
    07/05/2019
    10:00:00
    4
    8 v 10
    10:00:00
    3
    2 v 9
    07/05/2019
    10:00:00
    5
    7 v 6
    10:00:00
    4
    8 v 10 8 v 4
    07/05/2019
    10:00:00
    6
    11 v 4
    10:00:00
    5
    7 v 6 1 v 11
    14/05/2019
    10:00:00
    1
    7 v 5
    10:00:00
    6
    11 v 4
    14/05/2019
    10:00:00
    2
    6 v 10
    14/05/2019
    10:00:00
    3
    2 v 9
    14/05/2019
    10:00:00
    4
    8 v 4
    14/05/2019
    10:00:00
    5
    1 v 11


    btw. your result example has nothing to do with your source example
    This what I am trying to get to

    Spring and Fall Bocce League
    05/07/2019 05/14/2019 05/21/2019 05/28/2019 06/04/2019 06/11/2019 06/13/2019 06/18/2019 06/20/2019 06/25/2019 06/27/2019
    Time Court
    10:00 AM 1
    2
    3
    4
    5
    6
    7:00 PM 1
    2
    3
    4
    5
    6

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: split 1 column into multiple columns

    first: don't quote whole post!

    second: use Power Query (Get&Transform)

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Courts", Int64.Type}, {"Teams", type text}}),
        Pivot = Table.Pivot(Table.TransformColumnTypes(Type, {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Type, {{"Date", type text}}, "en-GB")[Date]), "Date", "Teams")
    in
        Pivot
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: split 1 column into multiple columns

    here is answer on your PM's question (PM is limited by the number of characters)

    source result
    Date Time Courts Teams Time Courts 07/05/2019 14/05/2019 21/05/2019 28/05/2019 04/06/2019
    07/05/2019
    10:00:00
    1
    1 v 9
    07:00:00
    3
    2 v 8
    07/05/2019
    10:00:00
    2
    3 v 5
    10:00:00
    1
    1 v 9 7 v 5 6 v 8 4 v 10
    07/05/2019
    10:00:00
    4
    8 v 10
    10:00:00
    2
    3 v 5 6 v 10 8 v 7
    07/05/2019
    10:00:00
    5
    7 v 6
    10:00:00
    3
    2 v 9 5 v 4 6 v 1
    07/05/2019
    10:00:00
    6
    11 v 4
    10:00:00
    4
    8 v 10 8 v 4 9 v 11
    14/05/2019
    10:00:00
    1
    7 v 5
    10:00:00
    5
    7 v 6 1 v 11 10 v 2 9 v 5
    14/05/2019
    10:00:00
    2
    6 v 10
    10:00:00
    6
    11 v 4 3 v 1 2 v 11
    14/05/2019
    10:00:00
    3
    2 v 9
    19:00:00
    2
    1 v 7
    14/05/2019
    10:00:00
    4
    8 v 4
    19:00:00
    4
    6 v 3
    14/05/2019
    10:00:00
    5
    1 v 11
    19:00:00
    5
    4 v 9
    21/05/2019
    10:00:00
    1
    6 v 8
    19:00:00
    6
    10 v 5
    21/05/2019
    10:00:00
    3
    5 v 4
    21/05/2019
    10:00:00
    4
    9 v 11
    21/05/2019
    10:00:00
    5
    10 v 2
    21/05/2019
    10:00:00
    6
    3 v 1
    28/05/2019
    10:00:00
    1
    4 v 10
    28/05/2019
    10:00:00
    2
    8 v 7
    28/05/2019
    10:00:00
    3
    6 v 1
    28/05/2019
    10:00:00
    5
    9 v 5
    28/05/2019
    10:00:00
    6
    2 v 11
    04/06/2019
    19:00:00
    2
    1 v 7
    04/06/2019
    07:00:00
    3
    2 v 8
    04/06/2019
    19:00:00
    4
    6 v 3
    04/06/2019
    19:00:00
    5
    4 v 9
    04/06/2019
    19:00:00
    6
    10 v 5


    M-code is exactly the same as in previous post, nothing changed, just paste data into blue table and use Refresh or Ctrl+Alt+F5

    is that what you want to achieve?

    and of course it would be good to learn more about Power Query
    Last edited by sandy666; Jul 10th, 2019 at 02:43 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: split 1 column into multiple columns

    here is an excel file with your example: download
    Last edited by sandy666; Jul 10th, 2019 at 03:22 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    New Member
    Join Date
    Sep 2008
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: split 1 column into multiple columns

    TY soooo much Sandy (the devil). I need to spend some time with power query so I can understand the solution you gave. That is more than I hoped for. Thank You Again
    Subject is closed.

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,577
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: split 1 column into multiple columns

    You are welcome

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

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
  •