Macro to convert data to a more standard form?

R

REvans81

New Member
Joined
Apr 25, 2018
Messages
21
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!!!
 
Sort by date Sort by votes
Glad to help & thanks for the feedback
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You must log in or register to reply here.

Similar threads

R
  • Locked
  • Question Question
Assistance requested writing macro to prepare data for pivot table. THANK YOU!
Replies
2
Views
507
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
425
johnnyL
J
F
  • Question Question
Large if returning name instead of value
Replies
1
Views
672
sandy666
sandy666
J
  • Question Question
Taking 9 columns and making them vertical with other data
2
Replies
10
Views
1K
jackson1990
J
J
  • Question Question
Using counta and countif
Replies
1
Views
647
RasGhul
RasGhul

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top