Macro Help

rrachlow1990 

This formula will 'unpivot' the raw data.  I began by unmerging all cells.

=LET(
    header, TAKE(matrix, 1),
    FillDown, LAMBDA(a, v, IF(v = "", a, v)),
    Faculty, INDEX(matrix, , 3),
    Course, INDEX(matrix, , 6),
    uFaculty, SORT(UNIQUE(TOCOL(Faculty, 1))),
    Semester, SCAN("", TAKE(matrix, , 1), FillDown),
    Department, SCAN("", INDEX(matrix, , 2), FillDown),
    FacultyNames, SCAN("", Faculty, FillDown),
    CrossListed, SCAN("", INDEX(matrix, , 4), FillDown),
    CourseDetails, TAKE(matrix, , -12),
    Record, HSTACK(Semester, Department, FacultyNames, CrossListed, CourseDetails),
    UnPivot, LAMBDA(a, v,
        LET(
            filtered, FILTER(Record, (FacultyNames = v) * (Course <> "Total"), ""),
            IFNA(VSTACK(a, filtered), "")
        )
    ),
    REDUCE(header, uFaculty, UnPivot)
)