Macro to convert data to a more standard form?

[REvans81]

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:

Student Name <tbody> </tbody>		Date	Class	Score Type	Score
Suzie H <tbody> </tbody>		1/2/2018 <tbody> </tbody>	282 - Math 1	Quiz <tbody> </tbody>	90
		1/8/2018 <tbody> </tbody>	778 - Geography <tbody> </tbody>	Quiz <tbody> </tbody>	87
		1/11/2018	117 - Social Studies <tbody> </tbody>	Homework <tbody> </tbody>	89
		2/4/2018 <tbody> </tbody>	282 - Math 1	Homework <tbody> </tbody>	89
		2/7/2018 <tbody> </tbody>	282 - Math 1	Homework <tbody> </tbody>	95
		2/12/2018 <tbody> </tbody>	778 - Geography <tbody> </tbody>	Quiz <tbody> </tbody>	77
Suzie H	Sponsor: Jim K	Average			87.83
Jimmy R		1/2/2018 <tbody> </tbody>	286 - Math 2 <tbody> </tbody>	Homework	85
		1/8/2018	134 - Earth Science <tbody> </tbody>	Quiz	89
		1/9/2018 <tbody> </tbody>	117 - Social Studies <tbody> </tbody>	Homework	84
		1/10/2018 <tbody> </tbody>	286 - Math 2	Homework	90
		2/2/2018 <tbody> </tbody>	778 - Geography <tbody> </tbody>	Homework	89
		2/8/2018 <tbody> </tbody>	778 - Geography <tbody> </tbody>	Project	85
		4/5/2018 <tbody> </tbody>	286 - Math 2	Homework	77
Jimmy R	Sponsor: Jim K	Average			85.57

<tbody>
</tbody>

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!!!

 

[REvans81]

Sorry, there are two different reports and I mixed them up. The sample posted is the report I'm working with

 

[Fluff]

seen post#21

 

[Fluff]

It would have been easier with the other report
As it's now pub time, I'll have a look tomorrow & sort something out.

 

[REvans81]

Have a wonderful evening! I'd buy you a pint if I was there

 

[Fluff]

Ok, give this a go

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

 

[REvans81]

I get an application/object defined error on

.Value = Rng.Offset(, -2).Resize(1, 1).Value

and zeros if I change it to

.Value = Rng.Offset(, -1).Resize(1, 1).Value

 

[Fluff]

Do you have any merged cells?

 

[REvans81]

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:

 

[Fluff]

Best option is to un-merge the merged cells.

   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")

 

[REvans81]

That was it. It's perfect

THANK YOU SO VERY MUCH!