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]

So it only needs minor modifications it seems. Here's what I have but it's not adding up the values for some reason, I get mostly zeros

Sub rearrangeData()

   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Rng As Range
   Dim Ary() As Long
   Dim i As Long
   
   i = 1
   Set Ws1 = Sheets("nrd")
   Set Ws2 = Sheets("nrdtest")
   'Set Ws2 = Sheets.Add(, Sheets(Sheets.Count))
   ' Ws2.Name = "test"
   With CreateObject("scripting.dictionary")
      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("C2", Ws1.Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).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(, 2).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]

EDIT: Not sure how I duplicated the reply....

 

[Fluff]

If the values you are trying to add are in col D try

If .exists(Cl.Value) Then Ary(.Item(Cl.Value)) = Ary(.Item(Cl.Value)) + Cl.Offset(, [COLOR=#ff0000]1[/COLOR]).Value

 

[REvans81]

I tried that too but it's still not adding up, I get names in rows and classes in columns perfectly but the results are all 0:00:00

 

[REvans81]

Sub rearrangeData()

   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Rng As Range
   Dim Ary() As Long
   Dim i As Long
   
   'Clear sheet
   Sheets("nrdtest").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")
      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("C2", Ws1.Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).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

 

[Fluff]

Ok, it's because you are adding up time, so make this change

   Dim Ary() As [COLOR=#ff0000]Double[/COLOR]

 

[REvans81]

Oh, duh... thank you for that. The only other issue now is it's only totaling up the results for each TA for the first date


So for this:

Totals are showing as follows

	001 - Admin	002 - Admin	282 - Math 1	286 - Math 2	778 - Geography	133 - SC	134 - Earth Science
Tim R	0:01:14	0:02:04	0:58:39	0:11:29	0:11:00	0:00:00	0:00:00
Stacy N	0:00:00	0:31:01	0:00:00	0:00:00	0:21:34	0:00:03	0:30:22

<tbody>
</tbody>

rather than totaling each date per person per class

 

[Fluff]

Can you post some actual data rather than an image.
There are tools to help with that here https://www.mrexcel.com/forum/about-board/508133-attachments.html
and you can test them out on the test board.

 

[REvans81]

Excel 2013/2016
	A	B	C	D
1	TA Name	Date	Class	Duration
2	Tim R	1/8/2018	Admin[001]	1:22:00
3			Math 1[282]	0:33:01
4			Admin[002]	0:45:00
5			Math 2[286]	0:15:00
6			Geography[778]	0:55:14
7				3:50:15
8		1/9/2018	Math 1[282]	0:00:45
9			Admin[002]	0:45:00
10			SC[133]	0:02:11
11			Admin[001]	0:58:39
12			Math 2[286]	0:04:52
13				1:51:27
14		1/10/2018	Admin[001]	1:22:00
15			Admin[002]	0:45:00
16				2:07:00
17		1/11/2018	[0]	0:04:36
18			Admin[001]	1:22:00
19			Admin[002]	0:45:00
20			SC[133]	0:55:00
21			Math 1[282]	0:25:00
22				3:31:36
23	Tim R			11:20:18
24	Stacy N	1/9/2018	Admin[001]	1:00:00
25			Admin[002]	0:30:00
26			SC[133]	0:15:00
27			Earth Science[134]	0:25:15
28				2:10:15
29		1/10/2018	Admin[001]	1:00:00
30			Admin[002]	0:30:00
31			SC[133]	0:15:00
32				1:45:00
33	Stacy N			3:55:15
TA

 

[Fluff]

In your image there are cells with "Total" & "Grand Total", but not in the sample you have just posted. Do they exist in your data?