Horizontal to Vertical

[gtd526]

NFL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Week	Week	Week		week	HOU	SEA	TEN	Week	JAX	WAS	NE	Week	MIA	CHI	LAR
2	1	2	3		1	KC	ATL	DEN	2	TEN	ARZ	SEA	3	JAX	ATL	BUF
3	HOU	JAX
4	KC	TEN
5	SEA	WAS
6	ATL	ARZ
7	TEN	NE
8	DEN	SEA
Sheet1

I'm looking to convert the Horizontal listing to Vertical, using a new column when "week" shows.
Vertical listing starts in Col A, then inserts a column to insert 2nd week (Col B), etc. 'insert' so it doesn't over-ride the original Horizontal listing (E:P).

 

[lrobbo314]

You can try using Power Query.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	Week16	Week17
2	HOU	CIN	MIA	DEN	TB	KC	NYG	ATL	GB	IND	ARZ	HOU	DAL	NE	LAC	MIN	MIA
3	KC	CLE	JAX	NYJ	CHI	BUF	PHI	CAR	SF	TEN	SEA	DET	BAL	LAR	OAK	NO	BUF
4	SEA	NYG	CHI	ARZ	CAR	CIN	DET	NE	DEN	TB	TEN	WAS	NO	DEN	BUF	SF	NO
5	ATL	CHI	ATL	CAR	ATL	IND	ATL	BUF	ATL	CAR	BAL	DAL	ATL	CAR	DEN	ARZ	CAR
6	CLE	ATL	LAR	IND	CIN	DET	PIT	TEN	SEA	HOU	DET	BAL	DET	HOU	CAR	TB	GB
7	BAL	DAL	BUF	CHI	BAL	JAX	BAL	CIN	BUF	CLE	CAR	PIT	CHI	CHI	GB	DET	CHI
8	NYJ	DET	WAS	JAX	JAX	ATL	NO	OAK	BAL	WAS	PHI	OAK	IND	DAL	HOU	MIA	BAL
9	BUF	GB	CLE	CIN	HOU	MIN	CAR	CLE	IND	DET	CLE	ATL	HOU	CIN	IND	OAK	CIN
10	OAK	MIN	TEN	CLE	OAK	WAS	CLE	IND	HOU	JAX	NE	LAC	CIN	GB	NYJ	DEN	PIT
11	CAR	IND	MIN	DAL	KC	NYG	CIN	DET	JAX	GB	HOU	BUF	MIA	DET	LAR	LAC	CLE
12	CHI	BUF	OAK	NO	DEN	BAL	GB	MIN	CAR	PHI	GB	NYG	JAX	TEN	DET	CLE	MIN
13	DET	MIA	NE	DET	NE	PHI	HOU	GB	KC	NYG	IND	CIN	MIN	JAX	TEN	NYJ	DET
14	IND	SF	SF	MIN	ARZ	CLE	LAC	NYJ	DET	CIN	PIT	TEN	OAK	KC	TB	NYG	TEN
15	JAX	NYJ	NYG	HOU	NYJ	PIT	MIA	KC	MIN	PIT	JAX	IND	NYJ	MIA	ATL	BAL	HOU
16	GB	LAR	CIN	SEA	PHI	HOU	CAR	LAR	CHI	BUF	ATL	CLE	WAS	ARZ	JAX	CIN	JAX
17	MIN	PHI	PHI	MIA	PIT	TEN	NO	MIA	TEN	ARZ	NO	JAX	PIT	NYG	BAL	HOU	IND
18	MIA	DEN	HOU	LAC	BUF	MIA	BUF	JAX	NYG	DEN	CIN	CAR	CLE	MIN	NE	CHI	LAC
19	NE	PIT	PIT	TB	TEN	DEN	NYJ	LAC	WAS	OAK	WAS	MIN	TEN	TB	MIA	JAX	KC
20	PHI	CAR	NYJ	PIT	LAR	NYJ	DAL	NO	OAK	NYJ	LAC	ARZ	LAR	IND	CHI	ATL	NYJ
21	WAS	TB	IND	TEN	WAS	LAC	WAS	CHI	LAC	MIA	DEN	NE	ARZ	OAK	MIN	KC	NE
22	LAC	JAX	CAR	BAL	MIA	GB	SEA	SF	MIA	SEA	DAL	MIA	NYG	NYJ	CLE	IND	DAL
23	CIN	TEN	LAC	WAS	SF	TB	ARZ	SEA	ARZ	LAR	MIN	NYJ	SEA	SEA	NYG	PIT	NYG
24	TB	WAS	DET	NYG	IND	LAR	KC	DAL	PIT	SF	KC	NO	PHI	ATL	SEA	CAR	WAS
25	NO	ARZ	ARZ	LAR	CLE	SF	DEN	PHI	DAL	NO	OAK	DEN	GB	LAC	WAS	WAS	PHI
26	ARZ	BAL	TB	NE	NYG	ARZ	SF	TB	NO	BAL	LAR	SF	NE	NO	PHI	LAR	ATL
27	SF	HOU	DEN	KC	DAL	DAL	NE	NYG	TB	NE	TB	LAR	LAC	PHI	ARZ	SEA	TB
28	DAL	KC	DAL	BUF	MIN		TB		NE	MIN		KC	DEN	WAS	KC	PHI	OAK
29	LAR	LAC	SEA	OAK	SEA		OAK		NYJ	CHI		TB	KC	SF	NO	DAL	DEN
30	PIT	NE	GB	PHI	LAC		CHI					CHI	BUF	PIT	SF	TEN	ARZ
31	NYG	SEA	NO	SF	NO		LAR					GB	SF	BUF	DAL	GB	LAR
32	TEN	NO	KC	ATL								SEA		BAL	PIT	BUF	SEA
33	DEN	OAK	BAL	GB								PHI		CLE	CIN	NE	SF
Sheet11

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Transpose0 = Table.Transpose(Source),
    WeekNum = Table.AddColumn(Transpose0, "Custom", each if [Column1] = "Week" then [Column1] & Text.From([Column2]) else null),
    FilledDown = Table.FillDown(WeekNum,{"Custom"}),
    FilteredRows = Table.SelectRows(FilledDown, each ([Column1] <> "Week")),
    UnpivotedColumns = Table.UnpivotOtherColumns(FilteredRows, {"Custom"}, "Attribute", "Value"),
    GroupedRows = Table.Group(UnpivotedColumns, {"Custom"}, {{"Count", each _, type table}}),
    Group = Table.TransformColumns(GroupedRows, {{"Count", each Text.Combine(_[Value],"-")}}),
    Split = Table.SplitColumn(Group, "Count", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4", "Count.5", "Count.6", "Count.7", "Count.8", "Count.9", "Count.10", "Count.11", "Count.12", "Count.13", "Count.14", "Count.15", "Count.16", "Count.17", "Count.18", "Count.19", "Count.20", "Count.21", "Count.22", "Count.23", "Count.24", "Count.25", "Count.26", "Count.27", "Count.28", "Count.29", "Count.30", "Count.31", "Count.32"}),
    Transpose1 = Table.Transpose(Split),
    PromoteHeaders = Table.PromoteHeaders(Transpose1, [PromoteAllScalars=true])
in
    PromoteHeaders

 

[gtd526]

You can try using Power Query.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	Week16	Week17
2	HOU	CIN	MIA	DEN	TB	KC	NYG	ATL	GB	IND	ARZ	HOU	DAL	NE	LAC	MIN	MIA
3	KC	CLE	JAX	NYJ	CHI	BUF	PHI	CAR	SF	TEN	SEA	DET	BAL	LAR	OAK	NO	BUF
4	SEA	NYG	CHI	ARZ	CAR	CIN	DET	NE	DEN	TB	TEN	WAS	NO	DEN	BUF	SF	NO
5	ATL	CHI	ATL	CAR	ATL	IND	ATL	BUF	ATL	CAR	BAL	DAL	ATL	CAR	DEN	ARZ	CAR
6	CLE	ATL	LAR	IND	CIN	DET	PIT	TEN	SEA	HOU	DET	BAL	DET	HOU	CAR	TB	GB
7	BAL	DAL	BUF	CHI	BAL	JAX	BAL	CIN	BUF	CLE	CAR	PIT	CHI	CHI	GB	DET	CHI
8	NYJ	DET	WAS	JAX	JAX	ATL	NO	OAK	BAL	WAS	PHI	OAK	IND	DAL	HOU	MIA	BAL
9	BUF	GB	CLE	CIN	HOU	MIN	CAR	CLE	IND	DET	CLE	ATL	HOU	CIN	IND	OAK	CIN
10	OAK	MIN	TEN	CLE	OAK	WAS	CLE	IND	HOU	JAX	NE	LAC	CIN	GB	NYJ	DEN	PIT
11	CAR	IND	MIN	DAL	KC	NYG	CIN	DET	JAX	GB	HOU	BUF	MIA	DET	LAR	LAC	CLE
12	CHI	BUF	OAK	NO	DEN	BAL	GB	MIN	CAR	PHI	GB	NYG	JAX	TEN	DET	CLE	MIN
13	DET	MIA	NE	DET	NE	PHI	HOU	GB	KC	NYG	IND	CIN	MIN	JAX	TEN	NYJ	DET
14	IND	SF	SF	MIN	ARZ	CLE	LAC	NYJ	DET	CIN	PIT	TEN	OAK	KC	TB	NYG	TEN
15	JAX	NYJ	NYG	HOU	NYJ	PIT	MIA	KC	MIN	PIT	JAX	IND	NYJ	MIA	ATL	BAL	HOU
16	GB	LAR	CIN	SEA	PHI	HOU	CAR	LAR	CHI	BUF	ATL	CLE	WAS	ARZ	JAX	CIN	JAX
17	MIN	PHI	PHI	MIA	PIT	TEN	NO	MIA	TEN	ARZ	NO	JAX	PIT	NYG	BAL	HOU	IND
18	MIA	DEN	HOU	LAC	BUF	MIA	BUF	JAX	NYG	DEN	CIN	CAR	CLE	MIN	NE	CHI	LAC
19	NE	PIT	PIT	TB	TEN	DEN	NYJ	LAC	WAS	OAK	WAS	MIN	TEN	TB	MIA	JAX	KC
20	PHI	CAR	NYJ	PIT	LAR	NYJ	DAL	NO	OAK	NYJ	LAC	ARZ	LAR	IND	CHI	ATL	NYJ
21	WAS	TB	IND	TEN	WAS	LAC	WAS	CHI	LAC	MIA	DEN	NE	ARZ	OAK	MIN	KC	NE
22	LAC	JAX	CAR	BAL	MIA	GB	SEA	SF	MIA	SEA	DAL	MIA	NYG	NYJ	CLE	IND	DAL
23	CIN	TEN	LAC	WAS	SF	TB	ARZ	SEA	ARZ	LAR	MIN	NYJ	SEA	SEA	NYG	PIT	NYG
24	TB	WAS	DET	NYG	IND	LAR	KC	DAL	PIT	SF	KC	NO	PHI	ATL	SEA	CAR	WAS
25	NO	ARZ	ARZ	LAR	CLE	SF	DEN	PHI	DAL	NO	OAK	DEN	GB	LAC	WAS	WAS	PHI
26	ARZ	BAL	TB	NE	NYG	ARZ	SF	TB	NO	BAL	LAR	SF	NE	NO	PHI	LAR	ATL
27	SF	HOU	DEN	KC	DAL	DAL	NE	NYG	TB	NE	TB	LAR	LAC	PHI	ARZ	SEA	TB
28	DAL	KC	DAL	BUF	MIN		TB		NE	MIN		KC	DEN	WAS	KC	PHI	OAK
29	LAR	LAC	SEA	OAK	SEA		OAK		NYJ	CHI		TB	KC	SF	NO	DAL	DEN
30	PIT	NE	GB	PHI	LAC		CHI					CHI	BUF	PIT	SF	TEN	ARZ
31	NYG	SEA	NO	SF	NO		LAR					GB	SF	BUF	DAL	GB	LAR
32	TEN	NO	KC	ATL								SEA		BAL	PIT	BUF	SEA
33	DEN	OAK	BAL	GB								PHI		CLE	CIN	NE	SF
Sheet11

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Transpose0 = Table.Transpose(Source),
    WeekNum = Table.AddColumn(Transpose0, "Custom", each if [Column1] = "Week" then [Column1] & Text.From([Column2]) else null),
    FilledDown = Table.FillDown(WeekNum,{"Custom"}),
    FilteredRows = Table.SelectRows(FilledDown, each ([Column1] <> "Week")),
    UnpivotedColumns = Table.UnpivotOtherColumns(FilteredRows, {"Custom"}, "Attribute", "Value"),
    GroupedRows = Table.Group(UnpivotedColumns, {"Custom"}, {{"Count", each _, type table}}),
    Group = Table.TransformColumns(GroupedRows, {{"Count", each Text.Combine(_[Value],"-")}}),
    Split = Table.SplitColumn(Group, "Count", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4", "Count.5", "Count.6", "Count.7", "Count.8", "Count.9", "Count.10", "Count.11", "Count.12", "Count.13", "Count.14", "Count.15", "Count.16", "Count.17", "Count.18", "Count.19", "Count.20", "Count.21", "Count.22", "Count.23", "Count.24", "Count.25", "Count.26", "Count.27", "Count.28", "Count.29", "Count.30", "Count.31", "Count.32"}),
    Transpose1 = Table.Transpose(Split),
    PromoteHeaders = Table.PromoteHeaders(Transpose1, [PromoteAllScalars=true])
in
    PromoteHeaders

I've never used Power Query. I have it downloaded and installed.
What's the procedure and where do I put this code?

 

[Fluff]

Have you tried my suggestion in post#9?

 

[gtd526]

Have you tried my suggestion in post#9?

Yes. It works WELL.
Thank you. Thx for the recommendation(s). "Practice your Patience".

 

[Fluff]

You're welcome & thanks for the feedback.

 

[gtd526]

Another option

Sub gtd()
   Dim Ary As Variant, Nary As Variant
   Dim c As Long, nr As Long, nc As Long
 
   Ary = Sheets("Raw").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To 34, 1 To UBound(Ary, 2))
 
   For c = 1 To UBound(Ary, 2)
      If LCase(Ary(1, c)) = "week" Then
         nr = 1: nc = nc + 1
      Else
         nr = nr + 2
      End If
      Nary(nr, nc) = Ary(1, c)
      Nary(nr + 1, nc) = Ary(2, c)
   Next c
   Sheets("Sheet1").Range("A1").Resize(UBound(Nary), nc).Value = Nary
End Sub

Hello,
I ran your macro successfully last time, but this time I get a "script out of range" error. Its right after the "End If" statement as noted below. When run line by line and it goes thru the error line many times, then I get the error. Any suggestions?

Sub Horizontal_Vertical()

'NFL Horizontal to Vertical for Schedule
'using 'Raw'= original "Weeks"=Results
'from Fluff @ Mr Excel
'Horizontal to Vertical post
'format all as General or Text, Not Date


   Dim Ary As Variant, Nary As Variant
   Dim c As Long, nr As Long, nc As Long
  
   Ary = Sheets("Raw").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To 34, 1 To UBound(Ary, 2))
  
   For c = 1 To UBound(Ary, 2)
      If LCase(Ary(1, c)) = "week" Then 'what the entire cell is equal to
         nr = 1: nc = nc + 1
      Else
         nr = nr + 2
      End If
      Nary(nr, nc) = Ary(1, c) 'script out of range
      Nary(nr + 1, nc) = Ary(2, c)
   Next c
   Sheets("Weeks").Range("A1").Resize(UBound(Nary), nc).Value = Nary
  
End Sub

Here is the some of the data I'm using, the range ends at column "KI":

NFL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	WEEK	DAL	PHI	PIT	NYJ	MIN	SF	JAX	SEA	ARZ	LAC	CLE	MIA	GB	DEN	CHI	BAL	WEEK	NYG	NO	CIN	HOU	LAR
2	1	TB	ATL	BUF	CAR	CIN	DET	HOU	IND	TEN	WAS	KC	NE	NO	NYG	LAR	LVR	2	WAS	CAR	CHI	CLE	IND
Raw

 

[Fluff]

Do you have any weeks with more than 16 Teams?

 

[gtd526]

Do you have any weeks with more than 16 Teams?

Yes. Some are labeled as TBA. I removed all the TBA's so its 16 or less, and the formula worked.
There are only 32 teams, so 16 would be the max number of games per week. I will make a note of "no more than 16".
Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.