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

 

[Julien888]

Hi, can you write the vertical and horizontal listings in two separate sheets? in that case you wouldn't have to bother with inserting columns and would simply need to use OFFSET().

 

[gtd526]

Hi, can you write the vertical and horizontal listings in two separate sheets? in that case you wouldn't have to bother with inserting columns and would simply need to use OFFSET().

Yes, two separate sheet are fine. thx

 

[lrobbo314]

Here is a VBA way. I put the original data on a sheet called 'Raw', and the results are shown on sheet 'Output'.

Book1
	A	B	C
1	Week1	Week2	Week3
2	HOU	JAX	MIA
3	SEA	WAS	CHI
4	TEN	NE	LAR
5	KC	TEN	JAX
6	ATL	ARZ	ATL
7	DEN	SEA	BUF
Output

Sub PX()
Dim Raw As Worksheet:       Set Raw = Sheets("Raw")
Dim OP As Worksheet:        Set OP = Sheets("Output")
Dim AR() As Variant:        AR = Raw.Range("A1:L2").Value2
Dim SD As Object:           Set SD = CreateObject("Scripting.Dictionary")
Dim c As Integer:           c = 1
Dim wNum As String
Dim r As Range

Dim SP() As String

For ro = LBound(AR) To UBound(AR)
    For co = LBound(AR) To UBound(AR, 2)
        If co Mod 4 = 1 Then
            wNum = AR(1, co)
        Else
            If SD.exists(wNum) Then
                SD(wNum) = SD(wNum) & "-" & AR(ro, co)
            Else
                SD(wNum) = AR(ro, co)
            End If
        End If
    Next co
Next ro

Set r = OP.Range("A1")
Set r = r.Resize(1, SD.Count)
r.Value = SD.keys

For Each k In SD.keys
    SP = Split(SD(k), "-")
    OP.Range(OP.Cells(2, c), OP.Cells(2, c)).Resize(UBound(SP) + 1, 1).Value2 = Application.Transpose(SP)
    c = c + 1
Next k

End Sub

 

[Julien888]

With Excel formulas, if your horizontal listing is on Sheet2 and your vertical listing on Sheet1, for the week number you just need to write in A2 =OFFSET(Sheet2!A2,0,3*(COLUMN()-1)) and expand to the right, then in A3 =OFFSET(Sheet2!B1,0,3*(COLUMN()-1)) and so on, once the 7 formulas are written, you just need to expand everything to the right.

 

[gtd526]

Here is a VBA way. I put the original data on a sheet called 'Raw', and the results are shown on sheet 'Output'.

Book1
	A	B	C
1	Week1	Week2	Week3
2	HOU	JAX	MIA
3	SEA	WAS	CHI
4	TEN	NE	LAR
5	KC	TEN	JAX
6	ATL	ARZ	ATL
7	DEN	SEA	BUF
Output

Sub PX()
Dim Raw As Worksheet:       Set Raw = Sheets("Raw")
Dim OP As Worksheet:        Set OP = Sheets("Output")
Dim AR() As Variant:        AR = Raw.Range("A1:L2").Value2
Dim SD As Object:           Set SD = CreateObject("Scripting.Dictionary")
Dim c As Integer:           c = 1
Dim wNum As String
Dim r As Range

Dim SP() As String

For ro = LBound(AR) To UBound(AR)
    For co = LBound(AR) To UBound(AR, 2)
        If co Mod 4 = 1 Then
            wNum = AR(1, co)
        Else
            If SD.exists(wNum) Then
                SD(wNum) = SD(wNum) & "-" & AR(ro, co)
            Else
                SD(wNum) = AR(ro, co)
            End If
        End If
    Next co
Next ro

Set r = OP.Range("A1")
Set r = r.Resize(1, SD.Count)
r.Value = SD.keys

For Each k In SD.keys
    SP = Split(SD(k), "-")
    OP.Range(OP.Cells(2, c), OP.Cells(2, c)).Resize(UBound(SP) + 1, 1).Value2 = Application.Transpose(SP)
    c = c + 1
Next k

End Sub

I didn't get the correct output.
I created a Raw sheet and Output sheet. Raw contains the actual data used for conversion.

Results of the macro:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Week	NYJ	GB	TB	TEN	ATL	SF	JAX	NE	CHI	OAK	DAL	DEN	CLE	LAC	PHI	IND	HOU	LAR	DET	MIN	NO	BAL	PIT	SEA	NYG	BUF	KC	WAS
2	HOU	OAK	MIA	ARZ	Week	DET	LAR	WAS	NO	LAR	SF	GB	ARZ	NO	PIT	BUF	NYG	MIA	ARZ	PIT	NYJ	SF	HOU	NO	Week	PHI	Week	ARZ	ATL
3	SEA	CHI	PHI	DAL	CIN	MIN	DEN	BAL	Week	WAS	CIN	KC	IND	MIN	BAL	LAR	MIN	NYJ	Week	NO	LAR	DAL	CAR	NE	DAL	NE	NE	MIN	OAK
4	CLE	IND	LAC	PIT	NYG	BUF	CAR	KC	MIA	TEN	HOU	Week	JAX	SEA	NYG	MIA	LAC	GB	NYG	CLE	JAX	TB	DET	Week	NO	DEN	DEN	IND	ARZ
5	TB	CAR	LAC	CHI	DET	WAS	PHI	OAK	BUF	NYG	LAC	SEA	NYJ	CAR	DAL	TEN	TB	DAL	PHI	IND	SF	SF	MIN	BAL	13	GB	CAR	SF	TB
6	CAR	DET	CAR	Week	PHI	BAL	PIT	DEN	PHI	OAK	NYG	KC	SEA	ARZ	KC	WAS	HOU	GB	TEN	CIN	TB	KC	Week	Week	BAL	LAC	HOU	PIT	DEN
7	CIN	TB	BUF	ATL	NE	CLE	TB	ARZ	ATL	MIA	TEN	SF	SF	MIA	LAR	SF	WAS	TEN	BUF	JAX	MIA	CHI	ARZ	LAC	ATL	KC	NYJ	Week	LAR
8	KC	ATL	PIT	SF	2	GB		PHI	TEN	CLE	WAS	NO	CLE	DET	TEN		DAL	DEN	DAL	TB	KC	SEA	PIT	TB			14	NYG
9	CIN	NO	ATL	LAR	CLE	IND		CIN	OAK	SEA	CLE	BAL	NYG	HOU	WAS		SEA	LAC	7	JAX	MIA	PHI	MIN	NYJ			LAR	TB
10	DET	WAS	CIN	NYG	CHI	MIA		BUF	IND	PHI	LAR	4	CIN	MIA	LAR		NO	TB	PHI	NE	LAC	NYG	TEN	10			CAR	OAK
11	GB	CAR	NE	LAR	CAR	NYG		LAC	OAK	ATL	NYG	ARZ	CAR	MIN	MIN		CAR	CIN	DAL	BAL	ARZ	LAR	JAX	CLE			GB	DAL
12	DEN	DET	WAS	8	CLE	PHI		NYJ	3	IND	PHI	DEN	CHI	NE	OAK		CLE	DET	GB	CAR	DET	TB	KC	15			IND	CIN
13	SEA	JAX	CIN	CAR	HOU	PIT		DAL	JAX	CAR	PIT	NE	CIN	NYJ	TB		DET	JAX	NE	CIN	OAK	GB	MIN	OAK			LAR	16
14	HOU	LAC	MIA					ARZ	OAK	BUF	BUF		MIA							HOU			CHI
15	WAS	ARZ	NO					HOU	SF	CLE	CIN		LAR							MIA			11
16	BAL	DEN	NYJ					LAC	GB	MIN	IND		NO							MIN			SEA
17	MIA	LAC	JAX					KC	CIN	WAS	PIT		NYJ							ATL			CLE
18	NO	PHI	NO					NE	CLE	LAC	TEN		BAL							BAL			DET
19	GB	SF	WAS					NYJ	DET	ARZ	ARZ		HOU							MIA			HOU
20	KC							NYG		NYG
21	ATL							PIT		WAS
22	BAL							ARZ		ARZ
23	CHI							KC		KC
24	ATL							NE		PIT
25	BAL							NYG		WAS
26	BUF
27	IND
28	JAX
29	SF
30	ATL
31	BUF
32	DET
33	DAL
34	PIT
35	BUF
36	CAR
37	CHI
Output

Here is the data for conversion:

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	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM
1	Week	HOU	SEA	CLE	NYJ	OAK	CHI	IND	GB	MIA	PHI	LAC	TB	ARZ	DAL	PIT	TEN	Week	CIN	NYG	ATL	DET	MIN	BUF	SF	LAR	DEN	CAR	JAX	WAS	BAL	KC	NE	NO	Week	MIA	CHI	LAR	WAS	TEN	OAK	SF	CIN	HOU	NYJ	CAR	DET	TB	DAL	GB	KC	Week	DEN	ARZ	IND	JAX	CLE	NO	MIN	SEA	LAC	PIT	BAL	NYG	NE	BUF	PHI	ATL	Week	TB	CAR	CIN	JAX	OAK	DEN	ARZ	PHI	BUF	LAR	MIA	IND	NYG	MIN	LAC	Week	KC	CIN	DET	ATL	WAS	BAL	CLE	HOU	MIA	NYJ	GB	LAR	ARZ	Week	NYG	DET	PIT	NO	CLE	GB	LAC	CAR	BUF	DAL	SEA	KC	SF	TB	CHI	Week	ATL	NE	TEN	OAK	IND	MIN	NYJ	LAR	JAX	NO	SF	DAL	TB	Week	GB	DEN	SEA	BAL	HOU	CAR	DET	CHI	NYG	OAK	MIA	PIT	NO	NE	Week	IND	TB	HOU	WAS	JAX	PHI	CIN	BUF	DEN	NYJ	SEA	SF	BAL	MIN	Week	ARZ	TEN	DET	PHI	NE	GB	PIT	ATL	CIN	LAC	DAL	KC	LAR	Week	HOU	WAS	BAL	OAK	LAC	NYG	TEN	CLE	CAR	ARZ	MIA	NO	SF	KC	CHI	SEA	Week	DAL	NO	DET	IND	CIN	JAX	OAK	WAS	CLE	LAR	NYG	PHI	NE	DEN	BUF	Week	NE	DEN	HOU	DAL	GB	TEN	KC	ARZ	MIN	IND	NYJ	ATL	NO	WAS	PIT	BAL	Week	LAC	BUF	CAR	HOU	NYJ	DET	TB	JAX	NE	CHI	CLE	SEA	PHI	KC	SF	PIT	Week	MIN	SF	TB	MIA	DEN	CLE	NYG	CIN	CHI	ATL	IND	CAR	LAR	PHI	TEN	BUF	Week	MIA	NO	GB	BAL	PIT	MIN	TEN	JAX	LAC	NYJ	DAL	WAS	ATL	OAK	ARZ	SEA
2	1	KC	ATL	BAL	BUF	CAR	DET	JAX	MIN	NE	WAS	CIN	NO	SF	LAR	NYG	DEN	2	CLE	CHI	DAL	GB	IND	MIA	NYJ	PHI	PIT	TB	TEN	ARZ	HOU	LAC	SEA	OAK	3	JAX	ATL	BUF	CLE	MIN	NE	NYG	PHI	PIT	IND	LAC	ARZ	DEN	SEA	NO	BAL	4	NYJ	CAR	CHI	CIN	DAL	DET	HOU	MIA	TB	TEN	WAS	LAR	KC	OAK	SF	GB	5	CHI	ATL	BAL	HOU	KC	NE	NYJ	PIT	TEN	WAS	SF	CLE	DAL	SEA	NO	6	BUF	IND	JAX	MIN	NYG	PHI	PIT	TEN	DEN	LAC	TB	SF	DAL	7	PHI	ATL	BAL	CAR	CIN	HOU	MIA	NO	NYJ	WAS	ARZ	DEN	NE	OAK	LAR	8	CAR	BUF	CIN	CLE	DET	GB	KC	MIA	LAC	CHI	SEA	PHI	NYG	9	SF	ATL	BUF	IND	JAX	KC	MIN	TEN	WAS	LAC	ARZ	DAL	TB	NYJ	10	TEN	CAR	CLE	DET	GB	NYG	PIT	ARZ	OAK	MIA	LAR	NO	NE	CHI	11	SEA	BAL	CAR	CLE	HOU	IND	JAX	NO	WAS	DEN	MIN	OAK	TB	12	DET	DAL	PIT	ATL	BUF	CIN	IND	JAX	MIN	NE	NYJ	DEN	LAR	TB	GB	PHI	13	BAL	ATL	CHI	HOU	MIA	MIN	NYJ	PIT	TEN	ARZ	SEA	GB	LAC	KC	SF	14	LAR	CAR	CHI	CIN	DET	JAX	MIA	NYG	TB	OAK	SEA	LAC	PHI	SF	BUF	CLE	15	OAK	DEN	GB	IND	LAR	TEN	ATL	BAL	MIA	MIN	NYG	WAS	ARZ	NO	DAL	CIN	16	NO	ARZ	DET	OAK	LAC	NYJ	BAL	HOU	JAX	KC	PIT	WAS	SEA	DAL	GB	NE	17	BUF	CAR	CHI	CIN	CLE	DET	HOU	IND	KC	NE	NYG	PHI	TB	DEN	LAR	SF
Raw

Thank you.

 

[gtd526]

I didn't get the correct output.
I created a Raw sheet and Output sheet. Raw contains the actual data used for conversion.

Results of the macro:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Week	NYJ	GB	TB	TEN	ATL	SF	JAX	NE	CHI	OAK	DAL	DEN	CLE	LAC	PHI	IND	HOU	LAR	DET	MIN	NO	BAL	PIT	SEA	NYG	BUF	KC	WAS
2	HOU	OAK	MIA	ARZ	Week	DET	LAR	WAS	NO	LAR	SF	GB	ARZ	NO	PIT	BUF	NYG	MIA	ARZ	PIT	NYJ	SF	HOU	NO	Week	PHI	Week	ARZ	ATL
3	SEA	CHI	PHI	DAL	CIN	MIN	DEN	BAL	Week	WAS	CIN	KC	IND	MIN	BAL	LAR	MIN	NYJ	Week	NO	LAR	DAL	CAR	NE	DAL	NE	NE	MIN	OAK
4	CLE	IND	LAC	PIT	NYG	BUF	CAR	KC	MIA	TEN	HOU	Week	JAX	SEA	NYG	MIA	LAC	GB	NYG	CLE	JAX	TB	DET	Week	NO	DEN	DEN	IND	ARZ
5	TB	CAR	LAC	CHI	DET	WAS	PHI	OAK	BUF	NYG	LAC	SEA	NYJ	CAR	DAL	TEN	TB	DAL	PHI	IND	SF	SF	MIN	BAL	13	GB	CAR	SF	TB
6	CAR	DET	CAR	Week	PHI	BAL	PIT	DEN	PHI	OAK	NYG	KC	SEA	ARZ	KC	WAS	HOU	GB	TEN	CIN	TB	KC	Week	Week	BAL	LAC	HOU	PIT	DEN
7	CIN	TB	BUF	ATL	NE	CLE	TB	ARZ	ATL	MIA	TEN	SF	SF	MIA	LAR	SF	WAS	TEN	BUF	JAX	MIA	CHI	ARZ	LAC	ATL	KC	NYJ	Week	LAR
8	KC	ATL	PIT	SF	2	GB		PHI	TEN	CLE	WAS	NO	CLE	DET	TEN		DAL	DEN	DAL	TB	KC	SEA	PIT	TB			14	NYG
9	CIN	NO	ATL	LAR	CLE	IND		CIN	OAK	SEA	CLE	BAL	NYG	HOU	WAS		SEA	LAC	7	JAX	MIA	PHI	MIN	NYJ			LAR	TB
10	DET	WAS	CIN	NYG	CHI	MIA		BUF	IND	PHI	LAR	4	CIN	MIA	LAR		NO	TB	PHI	NE	LAC	NYG	TEN	10			CAR	OAK
11	GB	CAR	NE	LAR	CAR	NYG		LAC	OAK	ATL	NYG	ARZ	CAR	MIN	MIN		CAR	CIN	DAL	BAL	ARZ	LAR	JAX	CLE			GB	DAL
12	DEN	DET	WAS	8	CLE	PHI		NYJ	3	IND	PHI	DEN	CHI	NE	OAK		CLE	DET	GB	CAR	DET	TB	KC	15			IND	CIN
13	SEA	JAX	CIN	CAR	HOU	PIT		DAL	JAX	CAR	PIT	NE	CIN	NYJ	TB		DET	JAX	NE	CIN	OAK	GB	MIN	OAK			LAR	16
14	HOU	LAC	MIA					ARZ	OAK	BUF	BUF		MIA							HOU			CHI
15	WAS	ARZ	NO					HOU	SF	CLE	CIN		LAR							MIA			11
16	BAL	DEN	NYJ					LAC	GB	MIN	IND		NO							MIN			SEA
17	MIA	LAC	JAX					KC	CIN	WAS	PIT		NYJ							ATL			CLE
18	NO	PHI	NO					NE	CLE	LAC	TEN		BAL							BAL			DET
19	GB	SF	WAS					NYJ	DET	ARZ	ARZ		HOU							MIA			HOU
20	KC							NYG		NYG
21	ATL							PIT		WAS
22	BAL							ARZ		ARZ
23	CHI							KC		KC
24	ATL							NE		PIT
25	BAL							NYG		WAS
26	BUF
27	IND
28	JAX
29	SF
30	ATL
31	BUF
32	DET
33	DAL
34	PIT
35	BUF
36	CAR
37	CHI
Output

Here is the data for conversion:

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	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM
1	Week	HOU	SEA	CLE	NYJ	OAK	CHI	IND	GB	MIA	PHI	LAC	TB	ARZ	DAL	PIT	TEN	Week	CIN	NYG	ATL	DET	MIN	BUF	SF	LAR	DEN	CAR	JAX	WAS	BAL	KC	NE	NO	Week	MIA	CHI	LAR	WAS	TEN	OAK	SF	CIN	HOU	NYJ	CAR	DET	TB	DAL	GB	KC	Week	DEN	ARZ	IND	JAX	CLE	NO	MIN	SEA	LAC	PIT	BAL	NYG	NE	BUF	PHI	ATL	Week	TB	CAR	CIN	JAX	OAK	DEN	ARZ	PHI	BUF	LAR	MIA	IND	NYG	MIN	LAC	Week	KC	CIN	DET	ATL	WAS	BAL	CLE	HOU	MIA	NYJ	GB	LAR	ARZ	Week	NYG	DET	PIT	NO	CLE	GB	LAC	CAR	BUF	DAL	SEA	KC	SF	TB	CHI	Week	ATL	NE	TEN	OAK	IND	MIN	NYJ	LAR	JAX	NO	SF	DAL	TB	Week	GB	DEN	SEA	BAL	HOU	CAR	DET	CHI	NYG	OAK	MIA	PIT	NO	NE	Week	IND	TB	HOU	WAS	JAX	PHI	CIN	BUF	DEN	NYJ	SEA	SF	BAL	MIN	Week	ARZ	TEN	DET	PHI	NE	GB	PIT	ATL	CIN	LAC	DAL	KC	LAR	Week	HOU	WAS	BAL	OAK	LAC	NYG	TEN	CLE	CAR	ARZ	MIA	NO	SF	KC	CHI	SEA	Week	DAL	NO	DET	IND	CIN	JAX	OAK	WAS	CLE	LAR	NYG	PHI	NE	DEN	BUF	Week	NE	DEN	HOU	DAL	GB	TEN	KC	ARZ	MIN	IND	NYJ	ATL	NO	WAS	PIT	BAL	Week	LAC	BUF	CAR	HOU	NYJ	DET	TB	JAX	NE	CHI	CLE	SEA	PHI	KC	SF	PIT	Week	MIN	SF	TB	MIA	DEN	CLE	NYG	CIN	CHI	ATL	IND	CAR	LAR	PHI	TEN	BUF	Week	MIA	NO	GB	BAL	PIT	MIN	TEN	JAX	LAC	NYJ	DAL	WAS	ATL	OAK	ARZ	SEA
2	1	KC	ATL	BAL	BUF	CAR	DET	JAX	MIN	NE	WAS	CIN	NO	SF	LAR	NYG	DEN	2	CLE	CHI	DAL	GB	IND	MIA	NYJ	PHI	PIT	TB	TEN	ARZ	HOU	LAC	SEA	OAK	3	JAX	ATL	BUF	CLE	MIN	NE	NYG	PHI	PIT	IND	LAC	ARZ	DEN	SEA	NO	BAL	4	NYJ	CAR	CHI	CIN	DAL	DET	HOU	MIA	TB	TEN	WAS	LAR	KC	OAK	SF	GB	5	CHI	ATL	BAL	HOU	KC	NE	NYJ	PIT	TEN	WAS	SF	CLE	DAL	SEA	NO	6	BUF	IND	JAX	MIN	NYG	PHI	PIT	TEN	DEN	LAC	TB	SF	DAL	7	PHI	ATL	BAL	CAR	CIN	HOU	MIA	NO	NYJ	WAS	ARZ	DEN	NE	OAK	LAR	8	CAR	BUF	CIN	CLE	DET	GB	KC	MIA	LAC	CHI	SEA	PHI	NYG	9	SF	ATL	BUF	IND	JAX	KC	MIN	TEN	WAS	LAC	ARZ	DAL	TB	NYJ	10	TEN	CAR	CLE	DET	GB	NYG	PIT	ARZ	OAK	MIA	LAR	NO	NE	CHI	11	SEA	BAL	CAR	CLE	HOU	IND	JAX	NO	WAS	DEN	MIN	OAK	TB	12	DET	DAL	PIT	ATL	BUF	CIN	IND	JAX	MIN	NE	NYJ	DEN	LAR	TB	GB	PHI	13	BAL	ATL	CHI	HOU	MIA	MIN	NYJ	PIT	TEN	ARZ	SEA	GB	LAC	KC	SF	14	LAR	CAR	CHI	CIN	DET	JAX	MIA	NYG	TB	OAK	SEA	LAC	PHI	SF	BUF	CLE	15	OAK	DEN	GB	IND	LAR	TEN	ATL	BAL	MIA	MIN	NYG	WAS	ARZ	NO	DAL	CIN	16	NO	ARZ	DET	OAK	LAC	NYJ	BAL	HOU	JAX	KC	PIT	WAS	SEA	DAL	GB	NE	17	BUF	CAR	CHI	CIN	CLE	DET	HOU	IND	KC	NE	NYG	PHI	TB	DEN	LAR	SF
Raw

Thank you.

Conversion must be B1&B2, C1&C2, D1&D2, etc. Then new column for Week 2 S1&S2, T1&T2, U1&U2, etc, then week 3, 4 ,5 etc.
Week 1
B1
B2
C1
C2
D1
D2
etc
new column for:
Week 2
S1
S2
T1
T2
etc

 

[lrobbo314]

Try this.

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	SEA	NYG	CHI	ARZ	CAR	CIN	DET	NE	DEN	TB	TEN	WAS	NO	DEN	BUF	SF	NO
4	CLE	ATL	LAR	IND	CIN	DET	PIT	TEN	SEA	HOU	DET	BAL	DET	HOU	CAR	TB	GB
5	NYJ	DET	WAS	JAX	JAX	ATL	NO	OAK	BAL	WAS	PHI	OAK	IND	DAL	HOU	MIA	BAL
6	OAK	MIN	TEN	CLE	OAK	WAS	CLE	IND	HOU	JAX	NE	LAC	CIN	GB	NYJ	DEN	PIT
7	CHI	BUF	OAK	NO	DEN	BAL	GB	MIN	CAR	PHI	GB	NYG	JAX	TEN	DET	CLE	MIN
8	IND	SF	SF	MIN	ARZ	CLE	LAC	NYJ	DET	CIN	PIT	TEN	OAK	KC	TB	NYG	TEN
9	GB	LAR	CIN	SEA	PHI	HOU	CAR	LAR	CHI	BUF	ATL	CLE	WAS	ARZ	JAX	CIN	JAX
10	MIA	DEN	HOU	LAC	BUF	MIA	BUF	JAX	NYG	DEN	CIN	CAR	CLE	MIN	NE	CHI	LAC
11	PHI	CAR	NYJ	PIT	LAR	NYJ	DAL	NO	OAK	NYJ	LAC	ARZ	LAR	IND	CHI	ATL	NYJ
12	LAC	JAX	CAR	BAL	MIA	GB	SEA	SF	MIA	SEA	DAL	MIA	NYG	NYJ	CLE	IND	DAL
13	TB	WAS	DET	NYG	IND	LAR	KC	DAL	PIT	SF	KC	NO	PHI	ATL	SEA	CAR	WAS
14	ARZ	BAL	TB	NE	NYG	ARZ	SF	TB	NO	BAL	LAR	SF	NE	NO	PHI	LAR	ATL
15	DAL	KC	DAL	BUF	MIN	BUF	TB	CAR	NE	MIN	SEA	KC	DEN	WAS	KC	PHI	OAK
16	PIT	NE	GB	PHI	LAC	IND	CHI	BUF	SF	TEN	BAL	CHI	BUF	PIT	SF	TEN	ARZ
17	TEN	NO	KC	ATL	CHI	JAX	PHI	CIN	ATL	CAR	CAR	SEA	BAL	BAL	PIT	BUF	SEA
18	KC	CLE	JAX	NYJ	ATL	MIN	ATL	CLE	BUF	CLE	CLE	DET	ATL	LAR	OAK	NO	BUF
19	ATL	CHI	ATL	CAR	BAL	NYG	BAL	DET	IND	DET	HOU	DAL	CHI	CAR	DEN	ARZ	CAR
20	BAL	DAL	BUF	CHI	HOU	PHI	CAR	GB	JAX	GB	IND	PIT	HOU	CHI	GB	DET	CHI
21	BUF	GB	CLE	CIN	KC	PIT	CIN	KC	KC	NYG	JAX	ATL	MIA	CIN	IND	OAK	CIN
22	CAR	IND	MIN	DAL	NE	TEN	HOU	MIA	MIN	PIT	NO	BUF	MIN	DET	LAR	LAC	CLE
23	DET	MIA	NE	DET	NYJ	DEN	MIA	LAC	TEN	ARZ	WAS	CIN	NYJ	JAX	TEN	NYJ	DET
24	JAX	NYJ	NYG	HOU	PIT	LAC	NO	CHI	WAS	OAK	DEN	IND	PIT	MIA	ATL	BAL	HOU
25	MIN	PHI	PHI	MIA	TEN	TB	NYJ	SEA	LAC	MIA	MIN	JAX	TEN	NYG	BAL	HOU	IND
26	NE	PIT	PIT	TB	WAS	SF	WAS	PHI	ARZ	LAR	OAK	MIN	ARZ	TB	MIA	JAX	KC
27	WAS	TB	IND	TEN	SF	DAL	ARZ	NYG	DAL	NO	TB	NE	SEA	OAK	MIN	KC	NE
28	CIN	TEN	LAC	WAS	CLE		DEN		TB	NE		NYJ	GB	SEA	NYG	PIT	NYG
29	NO	ARZ	ARZ	LAR	DAL		NE		NYJ	CHI		DEN	LAC	LAC	WAS	WAS	PHI
30	SF	HOU	DEN	KC	SEA		OAK					LAR	KC	PHI	ARZ	SEA	TB
31	LAR	LAC	SEA	OAK	NO		LAR					TB	SF	SF	NO	DAL	DEN
32	NYG	SEA	NO	SF								GB		BUF	DAL	GB	LAR
33	DEN	OAK	BAL	GB								PHI		CLE	CIN	NE	SF
Output

Sub PX()
Dim Raw As Worksheet:       Set Raw = Sheets("Raw")
Dim OP As Worksheet:        Set OP = Sheets("Output")
Dim AR() As Variant:        AR = Raw.Range("A1:JM2").Value2
Dim SD As Object:           Set SD = CreateObject("Scripting.Dictionary")
Dim c As Integer:           c = 1
Dim wNum As String
Dim r As Range

Dim SP() As String

For ro = LBound(AR) To UBound(AR)
    For co = LBound(AR) To UBound(AR, 2)
        If InStr(AR(1, co), "Week") > 0 Then
            wNum = AR(1, co) & AR(2, co)
        Else
            If SD.exists(wNum) Then
                SD(wNum) = SD(wNum) & "-" & AR(ro, co)
            Else
                SD(wNum) = AR(ro, co)
            End If
        End If
    Next co
Next ro

Set r = OP.Range("A1")
Set r = r.Resize(1, SD.Count)
r.Value = SD.keys

For Each k In SD.keys
    SP = Split(SD(k), "-")
    OP.Range(OP.Cells(2, c), OP.Cells(2, c)).Resize(UBound(SP) + 1, 1).Value2 = Application.Transpose(SP)
    c = c + 1
Next k

End Sub

 

[Fluff]

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

 

[gtd526]

Try this.

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	SEA	NYG	CHI	ARZ	CAR	CIN	DET	NE	DEN	TB	TEN	WAS	NO	DEN	BUF	SF	NO
4	CLE	ATL	LAR	IND	CIN	DET	PIT	TEN	SEA	HOU	DET	BAL	DET	HOU	CAR	TB	GB
5	NYJ	DET	WAS	JAX	JAX	ATL	NO	OAK	BAL	WAS	PHI	OAK	IND	DAL	HOU	MIA	BAL
6	OAK	MIN	TEN	CLE	OAK	WAS	CLE	IND	HOU	JAX	NE	LAC	CIN	GB	NYJ	DEN	PIT
7	CHI	BUF	OAK	NO	DEN	BAL	GB	MIN	CAR	PHI	GB	NYG	JAX	TEN	DET	CLE	MIN
8	IND	SF	SF	MIN	ARZ	CLE	LAC	NYJ	DET	CIN	PIT	TEN	OAK	KC	TB	NYG	TEN
9	GB	LAR	CIN	SEA	PHI	HOU	CAR	LAR	CHI	BUF	ATL	CLE	WAS	ARZ	JAX	CIN	JAX
10	MIA	DEN	HOU	LAC	BUF	MIA	BUF	JAX	NYG	DEN	CIN	CAR	CLE	MIN	NE	CHI	LAC
11	PHI	CAR	NYJ	PIT	LAR	NYJ	DAL	NO	OAK	NYJ	LAC	ARZ	LAR	IND	CHI	ATL	NYJ
12	LAC	JAX	CAR	BAL	MIA	GB	SEA	SF	MIA	SEA	DAL	MIA	NYG	NYJ	CLE	IND	DAL
13	TB	WAS	DET	NYG	IND	LAR	KC	DAL	PIT	SF	KC	NO	PHI	ATL	SEA	CAR	WAS
14	ARZ	BAL	TB	NE	NYG	ARZ	SF	TB	NO	BAL	LAR	SF	NE	NO	PHI	LAR	ATL
15	DAL	KC	DAL	BUF	MIN	BUF	TB	CAR	NE	MIN	SEA	KC	DEN	WAS	KC	PHI	OAK
16	PIT	NE	GB	PHI	LAC	IND	CHI	BUF	SF	TEN	BAL	CHI	BUF	PIT	SF	TEN	ARZ
17	TEN	NO	KC	ATL	CHI	JAX	PHI	CIN	ATL	CAR	CAR	SEA	BAL	BAL	PIT	BUF	SEA
18	KC	CLE	JAX	NYJ	ATL	MIN	ATL	CLE	BUF	CLE	CLE	DET	ATL	LAR	OAK	NO	BUF
19	ATL	CHI	ATL	CAR	BAL	NYG	BAL	DET	IND	DET	HOU	DAL	CHI	CAR	DEN	ARZ	CAR
20	BAL	DAL	BUF	CHI	HOU	PHI	CAR	GB	JAX	GB	IND	PIT	HOU	CHI	GB	DET	CHI
21	BUF	GB	CLE	CIN	KC	PIT	CIN	KC	KC	NYG	JAX	ATL	MIA	CIN	IND	OAK	CIN
22	CAR	IND	MIN	DAL	NE	TEN	HOU	MIA	MIN	PIT	NO	BUF	MIN	DET	LAR	LAC	CLE
23	DET	MIA	NE	DET	NYJ	DEN	MIA	LAC	TEN	ARZ	WAS	CIN	NYJ	JAX	TEN	NYJ	DET
24	JAX	NYJ	NYG	HOU	PIT	LAC	NO	CHI	WAS	OAK	DEN	IND	PIT	MIA	ATL	BAL	HOU
25	MIN	PHI	PHI	MIA	TEN	TB	NYJ	SEA	LAC	MIA	MIN	JAX	TEN	NYG	BAL	HOU	IND
26	NE	PIT	PIT	TB	WAS	SF	WAS	PHI	ARZ	LAR	OAK	MIN	ARZ	TB	MIA	JAX	KC
27	WAS	TB	IND	TEN	SF	DAL	ARZ	NYG	DAL	NO	TB	NE	SEA	OAK	MIN	KC	NE
28	CIN	TEN	LAC	WAS	CLE		DEN		TB	NE		NYJ	GB	SEA	NYG	PIT	NYG
29	NO	ARZ	ARZ	LAR	DAL		NE		NYJ	CHI		DEN	LAC	LAC	WAS	WAS	PHI
30	SF	HOU	DEN	KC	SEA		OAK					LAR	KC	PHI	ARZ	SEA	TB
31	LAR	LAC	SEA	OAK	NO		LAR					TB	SF	SF	NO	DAL	DEN
32	NYG	SEA	NO	SF								GB		BUF	DAL	GB	LAR
33	DEN	OAK	BAL	GB								PHI		CLE	CIN	NE	SF
Output

Sub PX()
Dim Raw As Worksheet:       Set Raw = Sheets("Raw")
Dim OP As Worksheet:        Set OP = Sheets("Output")
Dim AR() As Variant:        AR = Raw.Range("A1:JM2").Value2
Dim SD As Object:           Set SD = CreateObject("Scripting.Dictionary")
Dim c As Integer:           c = 1
Dim wNum As String
Dim r As Range

Dim SP() As String

For ro = LBound(AR) To UBound(AR)
    For co = LBound(AR) To UBound(AR, 2)
        If InStr(AR(1, co), "Week") > 0 Then
            wNum = AR(1, co) & AR(2, co)
        Else
            If SD.exists(wNum) Then
                SD(wNum) = SD(wNum) & "-" & AR(ro, co)
            Else
                SD(wNum) = AR(ro, co)
            End If
        End If
    Next co
Next ro

Set r = OP.Range("A1")
Set r = r.Resize(1, SD.Count)
r.Value = SD.keys

For Each k In SD.keys
    SP = Split(SD(k), "-")
    OP.Range(OP.Cells(2, c), OP.Cells(2, c)).Resize(UBound(SP) + 1, 1).Value2 = Application.Transpose(SP)
    c = c + 1
Next k

End Sub

Everything is working, except the order of the teams. Results are: B1, C1, D1, E1 not B1,B2,C1,C2,D1,D2