split 1 column into multiple columns

[breilly00]

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

<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

 

[sandy666]

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

 

[breilly00]

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 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

<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/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

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

 

[sandy666]

first: don't quote whole post!

second: use Power Query (Get&Transform)

[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]

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

 

[sandy666]

here is an excel file with your example: download

 

[breilly00]

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.

 

[sandy666]

You are welcome

Have a nice day