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
Sorry, there are two different reports and I mixed them up. The sample posted is the report I'm working with
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
seen post#21
 
Last edited:
Upvote 0
It would have been easier with the other report :)
As it's now pub time, I'll have a look tomorrow & sort something out.
 
Upvote 0
Ok, give this a go
Code: 
Sub rearrangeData()

   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Rng As Range
   Dim Ary() As Double
   Dim i As Long
   
   'Clear sheet
   Sheets("new").UsedRange.ClearContents
   
   i = 1
   Set Ws1 = Sheets("nrd")
   Set Ws2 = Sheets("nrdtest")
   'Set Ws2 = Sheets.Add(, Sheets(Sheets.Count))
   ' Ws2.Name = "test"
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) And Not IsEmpty(Cl.Value) Then
            .Add Cl.Value, i
            i = i + 1
         End If
      Next Cl
      Ws2.Range("A1").Value = "TA Name"
      Ws2.Range("B1").Resize(, .Count).Value = .keys
      
      For Each Rng In Ws1.Range("A:A").SpecialCells(xlBlanks).Offset(-1, 2).Areas
         ReDim Ary(1 To .Count)
         For Each Cl In Rng
            If .exists(Cl.Value) Then Ary(.Item(Cl.Value)) = Ary(.Item(Cl.Value)) + Cl.Offset(, 1).Value
         Next Cl
         With Ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Value = Rng.Offset(, -2).Resize(1, 1).Value
            .Offset(, 1).Resize(, UBound(Ary)).Value = Ary
         End With
      Next Rng
   End With
End Sub
 
Upvote 0
I get an application/object defined error on
Code: 
.Value = Rng.Offset(, -2).Resize(1, 1).Value

and zeros if I change it to
Code: 
.Value = Rng.Offset(, -1).Resize(1, 1).Value
 
Upvote 0
ugh, yes. I'm not sure why that doesn't come through on the data I uploaded.
I've tried a few different ways but I can't seem to get it to paste properly, here's a screen shot:

cwuKARY.png
 
Upvote 0
Best option is to un-merge the merged cells.
Code: 
   Set Ws1 = Sheets("nrd")
   Set Ws2 = Sheets("nrdtest")
   'Set Ws2 = Sheets.Add(, Sheets(Sheets.Count))
   ' Ws2.Name = "test"
   [COLOR=#ff0000]Ws1.UsedRange.MergeCells = False[/COLOR]
   With CreateObject("scripting.dictionary")
 
Upvote 0
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
511
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
437
johnnyL
J
F
  • Question Question
Large if returning name instead of value
Replies
1
Views
677
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
655
RasGhul
RasGhul

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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