I'm not really sure how to explain this... basically I get reports on student grades for tests/projects on different dates. THe report that's generated can export to excel but it's not the easiest to work with. I'd like to make a macro to convert this data to a different format (Like student names in rows, classes in columns, scores in the cells) but the classes and size of the data set is going to vary. Ultimately, I'd like to make a pivot table with the converted data as we have a third party requesting it in this format.
sample report:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165"]Student Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Class[/TD]
[TD]Score Type[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165"]Suzie H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/11/2018[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]117 - Social Studies[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/4/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/7/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Suzie H[/TD]
[TD]Sponsor: Jim K[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD]87.83[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jimmy R[/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]286 - Math 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/8/2018[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]134 - Earth Science[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quiz[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/9/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]117 - Social Studies[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/10/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]286 - Math 2[/TD]
[TD]Homework[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Project[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]4/5/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]286 - Math 2[/TD]
[TD]Homework[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Jimmy R[/TD]
[TD]Sponsor: Jim K[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD]85.57[/TD]
[/TR]
</tbody>[/TABLE]
The only items I really care about are the names, dates, class, and score (I don't need sponsor, average, or score type). I don't care what it looks like when it's converted, I just have to be able make a pivot table with the data.
I haven't had any formal training and I'm not very good at starting visual basic projects so I don't really know how to kick it off but nobody else here knows much of anything about excel. I know how to make pivot tables and I can usually tinker with the code after it's written in order to tweak it a bit.
If anyone is able to assist, I'd appreciate it!!!
sample report:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165"]Student Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Class[/TD]
[TD]Score Type[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165"]Suzie H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/11/2018[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]117 - Social Studies[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/4/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/7/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]282 - Math 1[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Homework[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]Quiz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Suzie H[/TD]
[TD]Sponsor: Jim K[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD]87.83[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jimmy R[/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]286 - Math 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/8/2018[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]134 - Earth Science[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Quiz[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/9/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]117 - Social Studies[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]1/10/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]286 - Math 2[/TD]
[TD]Homework[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/2/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Homework[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]2/8/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="width: 188"]778 - Geography[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Project[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 97"]
<tbody>[TR]
[TD="class: xl66, width: 97"]4/5/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]286 - Math 2[/TD]
[TD]Homework[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Jimmy R[/TD]
[TD]Sponsor: Jim K[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD]85.57[/TD]
[/TR]
</tbody>[/TABLE]
The only items I really care about are the names, dates, class, and score (I don't need sponsor, average, or score type). I don't care what it looks like when it's converted, I just have to be able make a pivot table with the data.
I haven't had any formal training and I'm not very good at starting visual basic projects so I don't really know how to kick it off but nobody else here knows much of anything about excel. I know how to make pivot tables and I can usually tinker with the code after it's written in order to tweak it a bit.
If anyone is able to assist, I'd appreciate it!!!