Need help connecting multiple data rows to one name

[dlevisay]

I'm attaching the example below. 2 tabs.

On the "Grades" tab, I input a lot of different grades for different kids. Each kid has a unique Student ID # that matches their name. Each kid has a unique Academic Coach and Team. These can change for different kids, but Kid 1 will always have the same Academic Coach and Team.

Once I input the data on the "Grades" tab, I need it pulled over to the "Grade Sheets" tab.

Here's where I have trouble - I need one grade sheet per student, but the student can be listed multiple times. I don't want to hand out four grade sheets to one student. Rather, I want to hand out one grade sheet with four grades on it.

I will have upwards of 1,000 different rows, so trying to match them up manually is exhausting.

Edit - If it would be easier to add another tab to have the data go there first to align for whatever reason, I'm fine with that. I'm not tied down to just two tabs. Any changes are ok!

[dlevisay]

Bump to the top

[oeldere]

Since you don't show the expect result (or I did not look well enough), I made a pivot table just as an example.

Please reply if this is what you want to analyse (see).

[dlevisay]

Not exactly. I need the data from "Grades" to be inputted into "Grade Sheets."

More specifically, on the grade sheets, I need it to find a student (Grades!C2:C26), write that name in Grade Sheets A4.

Once it has that name, match the name to "Academic Coach" (Grades!A2:A26), and write that name in Grades Sheets D4.

Also match "Week" from "Grades" to Grade Sheets I4.

Then, it needs to find the classes that the student is failing (all that are listed since I'll only list failing classes). It needs to list the first class failing from "Grades" to Grade Sheet B7. The grade for that class needs to be in B8 and the teacher's name in B9.

If there are multiple classes failed, it needs to find those and put them in the respective blanks.

I did the first example in the first sheet above, but not with formulas.

Once it is done, I need it to do the same over and over again until all students are listed on the Grade Sheets.

Hopefully this helps a bit more.

[oeldere]

Look at this result, also made with a pivot table.

[dlevisay]

That pivot table looks better. I switched the Student Name and Academic Coach so now it is sorted by Student.

Now that I have this info, how do I take it to the Grade Sheets page?

Index and match, I'm assuming? And if correct, please help with formulas if possible.

[oeldere]

You have all your information in your pivot table.

In that case there is no need for the sheet Grade Sheets.

[dlevisay]

I wasn't clear enough in the top.

The grade sheets are what I print out and hand to the students. They take these sheets to their teacher and have them signed. The data, such as Academic Coach, let's them know who to hand it in to. The grades let them know which classes to go to.

Sorry for the lack of info.

[oeldere]

The grade sheets are what I print out and hand to the students.

Why not add the file or print out to the Coach?

Less (people) to collect (from), less paper.

[dlevisay]

Just the way the procedure is done. Each coach will receive his stack of students that have grade sheets, meet with them, disseminate sheets, and then collect afterwards. The sheets are the tickets to tutorials for the students.

I have other data that the coaches have that show them all the kids who are failing. This is for the kids to have a grade sheet.

[oeldere]

In that case I would print this result for each student.

All information is available for the student.

Student Name Coaches Name Class Failing Week Teacher Totaal
Apple, Johnny Coach 1 Biology I 10 Biology Teacher 0

[JohnTopley]

See attached with VBA solution.


Click RUN on "Grade Sheets"

Sub Fill_Grade_Sheets()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim r As Long
Dim rr As Long
Dim ns As Long

Dim Student As String


Set ws1 = Worksheets("Grades")
Set ws2 = Worksheets("Grade Sheets")

r = 2
rr = 4
ns = 1

ws1.Activate

With ws1

Do While .Cells(r, 1) <> ""

    ' Fiil in a Grade sheet
    '
    rs = (ns - 1) * 25
    Student = .Cells(r, 1)
    
    ws2.Cells(rs + 4, 1) = .Cells(r, 3)
    ws2.Cells(rs + 4, 4) = .Cells(r, 1)
    ws2.Cells(rs + 4, 9) = .Cells(r, 8)
    
    Do While .Cells(r, 1) = Student
    
        ws2.Cells(rs + 7, 2) = .Cells(r, 4)
        ws2.Cells(rs + 8, 2) = .Cells(r, 5)
        ws2.Cells(rs + 9, 2) = .Cells(r, 6)
        
        r = r + 1
        rs = rs + 5
    
    Loop
    
    ns = ns + 1

Loop

End With

ws2.Activate

End Sub

[dlevisay]

Thank you. Trying that now!

[dlevisay]

The VBA code works! Thanks!

This saves me so much time.

[JohnTopley]

Amended code:

Sub Fill_Grade_Sheets()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim r As Long
Dim rr As Long
Dim ns As Long

Dim Student As String


Set ws1 = Worksheets("Grades")
Set ws2 = Worksheets("Grade Sheets")

r = 2
rr = 4
ns = 1

ws1.Activate

With ws1

Do While .Cells(r, 1) <> ""

    ' Fiil in a Grade sheet
    '
    rs = (ns - 1) * 25
    Student = .Cells(r, 3)
    
    ws2.Cells(rs + 4, 1) = .Cells(r, 3)
    ws2.Cells(rs + 4, 4) = .Cells(r, 1)
    ws2.Cells(rs + 4, 9) = .Cells(r, 8)
    
    Do While .Cells(r, 3) = Student
    
        ws2.Cells(rs + 7, 2) = .Cells(r, 4)
        ws2.Cells(rs + 8, 2) = .Cells(r, 5)
        ws2.Cells(rs + 9, 2) = .Cells(r, 6)
        
        r = r + 1
        rs = rs + 5
    
    Loop
    
    ns = ns + 1

Loop

End With

ws2.Activate

End Sub