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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,725
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,725
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,725
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,101,760
Messages
5,482,722
Members
407,360
Latest member
JTGF

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top