Convert 8 columns to two columns

[jkmclean]

How do you conver the following schedule

How do you convert the following 8 column schedule

​

Date Team ID

[TABLE="class: cms_table, width: 365"]
<tbody>[TR]
[TD="align: right"]September 5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD="align: right"]September 12[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]September 19[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD="align: right"]September 26[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="align: right"]October 3[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]October 10[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]October 17[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="align: right"]October 24[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]October 31[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD="align: right"]November[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]4


To a two column schedule
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]Sep-05 [/TD]
[TD="width: 64"]1[/TD]
[TD="width: 64"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD]8

[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-12 [/TD]
[TD]6[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
​

<strike></strike>

 

[DanteAmor]

Try this
Assuming that your data starts in A2, the result will be in columns K to M

Sub Convert_columns()
    Dim c As Range, j As Long
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For j = 2 To 8 Step 2
            Range("K" & Rows.Count).End(xlUp)(2).Value = c.Value
            Range("L" & Rows.Count).End(xlUp)(2).Resize(1, 2).Value = c.Offset(0, j - 1).Resize(1, 2).Value
        Next
    Next
End Sub

 

[bosco_yip]

Assume your data "Date and team ID" put in A1:I11 with header

In K2, copied down :

=IF((ROW(A1)<=40)*(MOD(ROW(A1),4)=1),OFFSET($A$1,INT((ROW(A1)-1)/4)+1,0),"")

In L2, copied right to M2 and copied down :

=IF(ROW(A1)<=40,OFFSET($A$1,INT((ROW(A1)-1)/4)+1,MOD(ROW(A1)-1,4)*2+COLUMN(A1)),"")

Regards
Bosco

 

[jkmclean]

Thank you.
I am not sure what this does but will try it.
pretty novice at this.

 

[jkmclean]

Thanks, will give it a try.

 

[DanteAmor]

You're welcome, let me know if you have any doubt

 

[jkmclean]

Hey it works.
I also figured out how to use it for other schedules of up to 40 teams.
Now, How to I call the routine from the spreadsheet.
Talk to you soon,

 

[jkmclean]

woops my mistake.
I tried a 16 team league and it only went through the first two weeks and stopped.
what values do I have to change to have it do say a 16 team league with 30 weeks, or say
a 10 team league with 30 weeks.

 

[jkmclean]

What has to change in the sub to say convert a 12 or 16 or 10 column to two columns?