How can I split Excel data from one row into multiple rows
Posted
by
Lenny
on Super User
See other posts from Super User
or by Lenny
Published on 2014-06-01T19:39:54Z
Indexed on
2014/06/01
21:38 UTC
Read the original article
Hit count: 579
Filed under:
microsoft-excel
|microsoft-excel-2010
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.
© Super User or respective owner
