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

September 5	1	2	3	4	5	6	7	8
September 12	6	8	5	7	2	4	1	3
September 19	5	4	1	8	7	3	2	6
September 26	3	6	7	2	1	5	8	4
October 3	7	1	4	6	3	8	5	2
October 10	2	3	8	5	4	1	6	7
October 17	4	7	6	1	8	2	3	5
October 24	5	6	7	8	3	4	1	2
October 31	2	4	1	3	5	7	6	8
November	7	3	2	6	1	8	5	4 To a two column schedule Sep-05 1 2 3 4 5 6 7 8 Sep-12 6 8 5 7 2 4 1 3 etc <colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody> </tbody> <strike></strike>

<tbody>
</tbody>

​

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