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

 

[Fluff]

Hi & welcome to the board.
How about

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("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Ws2 = Sheets.Add(, Sheets(Sheets.Count))
   Ws2.name = "[COLOR=#ff0000]Sheet2[/COLOR]"
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("D2", Ws1.Range("D" & 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 = "Student Name"
      Ws2.Range("B1").Resize(, .Count).Value = .keys
      For Each Rng In Ws1.Range("D2", Ws1.Range("D" & 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(, -3).Resize(1, 1).Value
            .Offset(, 1).Resize(, UBound(Ary)).Value = Ary
         End With
      Next Rng
   End With
End Sub

Change sheet names in red to suit

 

[REvans81]

Thank you for your reply and apologies for the duplicated post.

I'm getting a type mismatch error at

If .exists(Cl.Value) Then Ary(.Item(Cl.Value)) = Ary(.Item(Cl.Value)) + Cl.Offset(, 2).Value


and my output is (with the error)


Student Name Class 90 87 89 89 95 77


Could it be because some class names have special characters? (Like '741 - [AP] Math')

 

[Fluff]

What row is your header row in?

 

[REvans81]

Ahh, I had it in row 2.

Now I don't get errors but it doesn't bring in the class names still. Printing like this:

Student Name score score score score score score score score score score score score
Suzie H score score score score score score score score score score score score

What I'm looking for is for it to list one of each class (Each student may have different classes) and then sum of all scores for a student per class. Definitely moving in the right direction

 

[Fluff]

With data like

Excel 2013 32 bit
	A	B	C	D	E	F
1	Student Name		Date	Class	Score Type	Score
2	Suzie H		01/02/2018	282 - Math 1	Quiz	90
3			01/08/2018	778 - Geography	Quiz	87
4			01/11/2018	117 - Social Studies	Homework	89
5			02/04/2018	282 - Math 1	Homework	89
6			02/07/2018	282 - Math 1	Homework	95
7			02/12/2018	778 - Geography	Quiz	77
8	Suzie H	Sponsor: Jim K	Average			87.83
9
10	Jimmy R		01/02/2018	286 - Math 2	Homework	85
11			01/08/2018	134 - Earth Science	Quiz	89
12			01/09/2018	117 - Social Studies	Homework	84
13			01/10/2018	286 - Math 2	Homework	90
14			02/02/2018	778 - Geography	Homework	89
15			02/08/2018	778 - Geography	Project	85
16			04/05/2018	286 - Math 2	Homework	77
17	Jimmy R	Sponsor: Jim K	Average			85.57
Sheet1

I end up with

Excel 2013 32 bit
	A	B	C	D	E	F
1	Student Name	282 - Math 1	778 - Geography	117 - Social Studies	286 - Math 2	134 - Earth Science
2	Suzie H	274	164	89	0	0
3	Jimmy R	0	174	84	252	89
Sheet2

Does your data look anything like mine?

 

[REvans81]

I see what I did now, this works perfectly! THANK YOU!

Now I just need to duplicate it and modify it in a way to handle a similar but different report. Basically the same output,

Here's the report

I am immensely grateful for the time you put in here!

 

[Fluff]

Glad to help & thanks for the feedback.

If you need help modifying it, just let me know

 

[REvans81]

IF you have extra time and feel up to it, I'd love to see what it should look like for comparison (I have several other reports after this but this one will be the last I ask about). Please don't make this a priority though, you've already helped me quite a bit and I appreciate it.

 

[Fluff]

Tell you what.
You modify the code for your other report & post it here, then I'll have a look & suggest any changes.