split 1 column into multiple columns

breilly00

New Member
Joined
Sep 15, 2008
Messages
40
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
DateTimeCourtsTeams
05/07/201910:00 AM11 v 9
05/07/201910:00 AM23 v 5
05/07/201910:00 AM48 v 10
05/07/201910:00 AM57 v 6
05/07/201910:00 AM611 v 4
05/14/201910:00 AM17 v 5
05/14/201910:00 AM26 v 10
05/14/201910:00 AM32 v 9
05/14/201910:00 AM48 v 4
05/14/201910:00 AM51 v 11

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>






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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,836
something like this?

DateTimeCourtsTeamsTimeCourts07/05/201914/05/2019
07/05/2019​
10:00:00​
1​
1 v 9
10:00:00​
1​
1 v 97 v 5
07/05/2019​
10:00:00​
2​
3 v 5
10:00:00​
2​
3 v 56 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 108 v 4
07/05/2019​
10:00:00​
6​
11 v 4
10:00:00​
5​
7 v 61 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
 

breilly00

New Member
Joined
Sep 15, 2008
Messages
40
something like this?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Time[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Courts[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Teams[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Time[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Courts[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]07/05/2019[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14/05/2019[/COLOR]
07/05/2019​
10:00:00​
1​
1 v 9
10:00:00​
1​
1 v 97 v 5
07/05/2019​
10:00:00​
2​
3 v 5
10:00:00​
2​
3 v 56 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 108 v 4
07/05/2019​
10:00:00​
6​
11 v 4
10:00:00​
5​
7 v 61 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

<tbody>
</tbody>


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/201905/14/201905/21/201905/28/201906/04/201906/11/201906/13/201906/18/201906/20/201906/25/201906/27/2019
TimeCourt
10:00 AM1
2
3
4
5
6
7:00 PM1
2
3
4
5
6

<colgroup><col><col><col span="11"></colgroup><tbody>
</tbody>
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,836
first: don't quote whole post!

second: use Power Query (Get&Transform)

Code:
[SIZE=1]// 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[/SIZE]
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,836
here is answer on your PM's question (PM is limited by the number of characters)

sourceresult
DateTimeCourtsTeamsTimeCourts07/05/201914/05/201921/05/201928/05/201904/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 97 v 56 v 84 v 10
07/05/2019​
10:00:00​
4​
8 v 10
10:00:00​
2​
3 v 56 v 108 v 7
07/05/2019​
10:00:00​
5​
7 v 6
10:00:00​
3​
2 v 95 v 46 v 1
07/05/2019​
10:00:00​
6​
11 v 4
10:00:00​
4​
8 v 108 v 49 v 11
14/05/2019​
10:00:00​
1​
7 v 5
10:00:00​
5​
7 v 61 v 1110 v 29 v 5
14/05/2019​
10:00:00​
2​
6 v 10
10:00:00​
6​
11 v 43 v 12 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:

breilly00

New Member
Joined
Sep 15, 2008
Messages
40
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,646
Messages
5,488,079
Members
407,624
Latest member
NatashaGillWH

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top