How can I split Excel data from one row into multiple rows

Question

Good afternoon,

Is there a way to split data from one row and store to separate rows? I have a large file that contains scheduling information and I'm trying to develop a list that comprises each combination of course, day, term and period per line. For example I have a file similiar to this:

Crs:Sn  Title   Tchr    TchrName    Room    Days    Terms   Periods
7014:01 English I   678 JUNG    300 M,T,W,R,F   3,4 2,3
1034:02 English II  123 MOORE   352 M,T,W,R,F   3   4
7144:02 Algebra 238 VYSOTSKY    352 M,T,W,R,F   3,4 3,4
0180:06 Pub Speaking    23  ROSEN   228 M,T,W,R,F   3,4 5
7200:03 PE I    244 HARILAOU    GYM 4   M,T,W,R,F   1,2,3   3
2101:01 Physics/Lab 441 JONES   348 M,T,W,R,F   1,2,3,4 2,3

Should extract to this in an excel file:
Crs:Sn  Title           Tchr#   Tchr    Room    Days    Terms   Period
7014:01 English I   678 JUNG    300 M   3   2
7014:01 English I   678 JUNG    300 T   3   2
7014:01 English I   678 JUNG    300 W   3   2
7014:01 English I   678 JUNG    300 R   3   2
7014:01 English I   678 JUNG    300 F   3   2
7014:01 English I   678 JUNG    300 M   4   2
7014:01 English I   678 JUNG    300 T   4   2
7014:01 English I   678 JUNG    300 W   4   2
7014:01 English I   678 JUNG    300 R   4   2
7014:01 English I   678 JUNG    300 F   4   2
7014:01 English I   678 JUNG    300 M   3   3
7014:01 English I   678 JUNG    300 T   3   3
7014:01 English I   678 JUNG    300 W   3   3
7014:01 English I   678 JUNG    300 R   3   3
7014:01 English I   678 JUNG    300 F   3   3
7014:01 English I   678 JUNG    300 M   4   3
7014:01 English I   678 JUNG    300 T   4   3
7014:01 English I   678 JUNG    300 W   4   3
7014:01 English I   678 JUNG    300 R   4   3
7014:01 English I   678 JUNG    300 F   4   3
1034:02 English II  123 MOORE   352 M   3   4
1034:02 English II  123 MOORE   352 T   3   4
1034:02 English II  123 MOORE   352 W   3   4
1034:02 English II  123 MOORE   352 R   3   4
1034:02 English II  123 MOORE   352 F   3   4
7144:02 Algebra 238 VYSOTSKY    352 M   3   3
7144:02 Algebra 238 VYSOTSKY    352 T   3   3
7144:02 Algebra 238 VYSOTSKY    352 W   3   3
7144:02 Algebra 238 VYSOTSKY    352 R   3   3
7144:02 Algebra 238 VYSOTSKY    352 F   3   3
7144:02 Algebra 238 VYSOTSKY    352 M   4   3
7144:02 Algebra 238 VYSOTSKY    352 T   4   3
7144:02 Algebra 238 VYSOTSKY    352 W   4   3
7144:02 Algebra 238 VYSOTSKY    352 R   4   3
7144:02 Algebra 238 VYSOTSKY    352 F   4   3
7144:02 Algebra 238 VYSOTSKY    352 M   3   4
7144:02 Algebra 238 VYSOTSKY    352 T   3   4
7144:02 Algebra 238 VYSOTSKY    352 W   3   4
7144:02 Algebra 238 VYSOTSKY    352 R   3   4
7144:02 Algebra 238 VYSOTSKY    352 F   3   4
7144:02 Algebra 238 VYSOTSKY    352 M   4   4
7144:02 Algebra 238 VYSOTSKY    352 T   4   4
7144:02 Algebra 238 VYSOTSKY    352 W   4   4
7144:02 Algebra 238 VYSOTSKY    352 R   4   4
7144:02 Algebra 238 VYSOTSKY    352 F   4   4
0180:06 Pub Speaking    23  ROSEN   228 M   3   5
0180:06 Pub Speaking    23  ROSEN   228 T   3   5
0180:06 Pub Speaking    23  ROSEN   228 W   3   5
0180:06 Pub Speaking    23  ROSEN   228 R   3   5
0180:06 Pub Speaking    23  ROSEN   228 F   3   5
0180:06 Pub Speaking    23  ROSEN   228 M   4   5
0180:06 Pub Speaking    23  ROSEN   228 T   4   5
0180:06 Pub Speaking    23  ROSEN   228 W   4   5
0180:06 Pub Speaking    23  ROSEN   228 R   4   5
0180:06 Pub Speaking    23  ROSEN   228 F   4   5
7200:03 PE I    244 HARILAOU    GYM 4   M   1   3
7200:03 PE I    244 HARILAOU    GYM 4   M   2   3
7200:03 PE I    244 HARILAOU    GYM 4   M   3   3
7200:03 PE I    244 HARILAOU    GYM 4   T   1   3
7200:03 PE I    244 HARILAOU    GYM 4   T   2   3
7200:03 PE I    244 HARILAOU    GYM 4   T   3   3
7200:03 PE I    244 HARILAOU    GYM 4   W   1   3
7200:03 PE I    244 HARILAOU    GYM 4   W   2   3
7200:03 PE I    244 HARILAOU    GYM 4   W   3   3
7200:03 PE I    244 HARILAOU    GYM 4   R   1   3
7200:03 PE I    244 HARILAOU    GYM 4   R   2   3
7200:03 PE I    244 HARILAOU    GYM 4   R   3   3
7200:03 PE I    244 HARILAOU    GYM 4   F   1   3
7200:03 PE I    244 HARILAOU    GYM 4   F   2   3
7200:03 PE I    244 HARILAOU    GYM 4   F   3   3
2101:01 Physics/Lab 441 JONES   348 M   1   2
2101:01 Physics/Lab 441 JONES   348 M   2   2
2101:01 Physics/Lab 441 JONES   348 M   3   2
2101:01 Physics/Lab 441 JONES   348 M   4   2
2101:01 Physics/Lab 441 JONES   348 T   1   2
2101:01 Physics/Lab 441 JONES   348 T   2   2
2101:01 Physics/Lab 441 JONES   348 T   3   2
2101:01 Physics/Lab 441 JONES   348 T   4   2
2101:01 Physics/Lab 441 JONES   348 W   1   2
2101:01 Physics/Lab 441 JONES   348 W   2   2
2101:01 Physics/Lab 441 JONES   348 W   3   2
2101:01 Physics/Lab 441 JONES   348 W   4   2
2101:01 Physics/Lab 441 JONES   348 R   1   2
2101:01 Physics/Lab 441 JONES   348 R   2   2
2101:01 Physics/Lab 441 JONES   348 R   3   2
2101:01 Physics/Lab 441 JONES   348 R   4   2
2101:01 Physics/Lab 441 JONES   348 F   1   2
2101:01 Physics/Lab 441 JONES   348 F   2   2
2101:01 Physics/Lab 441 JONES   348 F   3   2
2101:01 Physics/Lab 441 JONES   348 F   4   2
2101:01 Physics/Lab 441 JONES   348 M   1   3
2101:01 Physics/Lab 441 JONES   348 M   2   3
2101:01 Physics/Lab 441 JONES   348 M   3   3
2101:01 Physics/Lab 441 JONES   348 M   4   3
2101:01 Physics/Lab 441 JONES   348 T   1   3
2101:01 Physics/Lab 441 JONES   348 T   2   3
2101:01 Physics/Lab 441 JONES   348 T   3   3
2101:01 Physics/Lab 441 JONES   348 T   4   3
2101:01 Physics/Lab 441 JONES   348 W   1   3
2101:01 Physics/Lab 441 JONES   348 W   2   3
2101:01 Physics/Lab 441 JONES   348 W   3   3
2101:01 Physics/Lab 441 JONES   348 W   4   3
2101:01 Physics/Lab 441 JONES   348 R   1   3
2101:01 Physics/Lab 441 JONES   348 R   2   3
2101:01 Physics/Lab 441 JONES   348 R   3   3
2101:01 Physics/Lab 441 JONES   348 R   4   3
2101:01 Physics/Lab 441 JONES   348 F   1   3
2101:01 Physics/Lab 441 JONES   348 F   2   3
2101:01 Physics/Lab 441 JONES   348 F   3   3
2101:01 Physics/Lab 441 JONES   348 F   4   3

I'm trying to avoid going line by line separating the data. I'm not well versed on the VBA functionality of Excel, but would like to get started using it.

Any help would be greatly appreciated.

Answer 1

I would use the Power Query Add-In - it has Split and Unpivot commands that you can layer one after another to transform your table.

Its a bit hard to read from your example, but I think I see you have multiple "cells" within the Days column, separated by commas? So I would use a Split command to bust that out into multiple columns, then an Unpivot command to transform those columns into multiple rows.

I would then repeat that for Terms and Periods (if I read your requirements correctly).

Here's where you can get Power Query:

http://www.microsoft.com/en-au/download/details.aspx?id=39379