Filling in "assuming" data on Excel

Question

Hi,

I am working on a worksheet in excel where we have generated a homework report for our students.

The homework report only shows the subjects for ones that were either done poorly or were not submitted.

What I want to generate with it is the homework "done/submitted" report by comparing data from the homework list worksheet.

for example we have data for a student that has not done homework for their Science subject, so now because there is no data for the other subjects, it will be "assumed" as completed.

Is there a way to create a code or macro that can generate this "assumed" list on a separate worksheet since there is no data to pull from?

Answer 1

Dear @HShahzada,

Good day, and thank you for reaching out to the Q&A Forum. I understand that you want to create a complete homework report that includes all subjects for each student. Since your current report only shows subjects that were done poorly or not submitted, the missing subjects can be treated as Completed or Assumed Completed.

This can be handled with a VBA macro. The macro below reads the existing report, checks each student against a full subject list, and creates a new worksheet showing both the reported issues and the assumed completed subjects.

Please try the following steps:

1. Press Alt + F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the code below.
4. Update the sheet name and subject list as needed.
5. Close the VBA editor.
6. Run the macro using Alt + F8 > GenerateFullHomeworkReport > Run.

Sub GenerateFullHomeworkReport()
 
    Dim wsSource As Worksheet
    Dim wsOutput As Worksheet
    Dim LastRow As Long, i As Long, OutputRow As Long
    Dim StudentDict As Object
    Dim StudentInfo As Object
    Dim BadSubjects As Object
   
    Dim Student As String
    Dim Subject As String
    Dim Status As String
    Dim YearVal As String
    Dim GroupVal As String
   
    Dim AllSubjects As Variant
    Dim studentKey As Variant
    Dim s As Variant
    Dim parts As Variant
   
    ' ================== SETTINGS ==================
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
   
    ' List ALL subjects in your curriculum
    AllSubjects = Array("Math", "Science", "English", "History", "Geography", _
                        "Physics", "Chemistry", "Biology", "Art", "PE")
   
    ' Delete old output sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Full Homework Report").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
   
    ' Create new output sheet
    Set wsOutput = ThisWorkbook.Sheets.Add
    wsOutput.Name = "Full Homework Report"
   
    ' Headers
    wsOutput.Range("A1:E1").Value = Array("Year", "Group", "Student Name", "Status", "Subject")
    OutputRow = 2
   
    LastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
   
    Set StudentDict = CreateObject("Scripting.Dictionary")
   
    ' Collect bad/missing subjects per student
    For i = 2 To LastRow
   
        YearVal = Trim(wsSource.Cells(i, 1).Value)
        GroupVal = Trim(wsSource.Cells(i, 2).Value)
        Student = Trim(wsSource.Cells(i, 3).Value)
        Status = Trim(wsSource.Cells(i, 4).Value)
        Subject = Trim(wsSource.Cells(i, 5).Value)
       
        If Student = "" Or Subject = "" Then GoTo NextRow
       
        ' Use Year + Group + Student as the unique key
        studentKey = YearVal & "|" & GroupVal & "|" & Student
       
        If Not StudentDict.Exists(studentKey) Then
            Set StudentDict(studentKey) = CreateObject("Scripting.Dictionary")
        End If
       
        StudentDict(studentKey)(Subject) = Status
       
NextRow:
    Next i
   
    ' Generate full report
    For Each studentKey In StudentDict.Keys
   
        parts = Split(studentKey, "|")
       
        YearVal = parts(0)
        GroupVal = parts(1)
        Student = parts(2)
       
        Set BadSubjects = StudentDict(studentKey)
       
        For Each s In AllSubjects
       
            wsOutput.Cells(OutputRow, 1).Value = YearVal
            wsOutput.Cells(OutputRow, 2).Value = GroupVal
            wsOutput.Cells(OutputRow, 3).Value = Student
            wsOutput.Cells(OutputRow, 5).Value = s
           
            If BadSubjects.Exists(s) Then
                wsOutput.Cells(OutputRow, 4).Value = BadSubjects(s)
            Else
                wsOutput.Cells(OutputRow, 4).Value = "Completed"
            End If
           
            OutputRow = OutputRow + 1
           
        Next s
       
    Next studentKey
   
    wsOutput.Columns("A:E").AutoFit
   
    MsgBox "Full Homework Report generated successfully!", vbInformation
 
End Sub

The macro will generate rows for all subjects. Science will keep the original status, and the other subjects will be marked as Completed

Please note that this macro can only generate results for students who already appear in the source report. If you also need to include students who have no poor/not-submitted homework at all, you will need a full student list as well.

I hope this information is helpful. Please try the steps above and let me know if it works for you. If you have any further questions or need help adjusting the code to match your exact worksheet layout, please feel free to share more details in the comments. Thank you for your patience and understanding. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.