Search code examples
pivot-table

Creating an Excel Pivot Table with Weekdays as Columns and Lessons as Rows for people

Question:

I have a dataset (xlsx) where teachers have recorded their availability for lessons throughout the week. Each entry includes the weekday and the lesson period (e.g., Monday Lesson 1 8:00-8:45). If they are note available there is a 1, otherwise the cell is empty. The rows contain the teachers names and the colums contain the weekday and the lesson period (Mo Lesson 1, Tue Lesson 1,..., Fr Lesson 11).

I want to create a Pivot Table in Excel where the columns represent weekdays (Monday to Friday) and the rows represent lesson periods (1st to 11th). Additionally, I want the ability to filter the Pivot Table based on specific teachers.

Could anyone guide me on how to efficiently structure my data and create such a Pivot Table? Any help or suggestions would be greatly appreciated!

For the Pivot Table itself, I attempted to organize the data with weekdays as columns and lesson periods as rows. I expected that by dragging the appropriate fields into the Pivot Table's rows and columns, I would be able to achieve the desired matrix-like layout but it did not work.

Solution

  • Assuming your data looks something like this (4 teachers = 4 rows, 11 lessons * 5 days = 55 columns) where "1" populated cells = unavailable and blank cells = available :

    Data

    You should transform the data to look like this (long format) :

    Transform

    It's been done here with PowerQuery (unpivot the 55 columns, splitting one of the resulting column into 3 columns ("Day";"Lesson";"Time") and replace blank data with 0).

    Then, you can produce a pivot table, like this one :

    Pivot

    With "Lessons" in rows field, "Day" in columns field, and "Teachers" in filter field. We also put "Availabality" in the filter area (0 : teacher's available ; 1 : teacher's not available). "Availability" is used to count in the "Values" area of the pivot table.

    EDIT : To automate the export in PDF format of one pivot table for each teacher, you could use VBA.

    Sub ExportPDFTeachers()
    Dim Feuille As Worksheet
    Dim PTFiltre As PivotTable
    Dim PF As PivotField
    Dim PItem As PivotItem
    Dim NomPDF As String

    ' Name of the sheet containing the pivot table
    Dim NomFeuille As String
    NomFeuille = "pivot_table"
    
    ' Name of the filter field to interact with
    Dim NomEnseignant As String
    NomEnseignant = "Teachers"
    
    ' Set the sheet containing the pivot table
    Set Feuille = ThisWorkbook.Worksheets(NomFeuille)
    
    ' Set the pivot table
    Set PTFiltre = Feuille.PivotTables(1)
    
    ' Set the filter field inside the pivot table
    Set PF = PTFiltre.PivotFields(NomEnseignant)
    
   ' Clear all filters from the "Teachers" field
    PF.ClearAllFilters
    
    ' Map each element of the "Teachers" field
    For Each PItem In PF.PivotItems
    
        ' Set all elements to false except the current element
        For Each Item In PF.PivotItems
            If Item.Name <> PItem.Name Then
                Item.Visible = False
            End If
        Next Item
        
        ' Select the current item
        PItem.Visible = True
        
        ' Filename of the PDF
        NomPDF = PItem.Name & ".pdf"
        
        ' Export the sheet to PDF
        Feuille.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NomPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
        ' Clear the filter
        PF.ClearAllFilters
    Next PItem
    
    MsgBox "PDF has been generated"
End Sub

    Output :

    PDF