Pivot table with double rows or going from wide to long in Google Sheets

Question

How can I go from wide to long in Google Sheets based on two different columns or create a pivot table where I specify two different columns from the original matrix as rows?

Please see example for intended effect:

Answer 1

Here's a fairly simple approach. I don't think you can use a pivot table because the values have to be summaries of a numeric value, or at least counts, not a string value.

To get the times:

=transpose(C2:C)

To get the lecturers (fairly big assumption that there are no lecturers that work only as assistants but this can be changed later):

=unique(B:B)

If there are additional lecturers working only as assistants:

=unique({B:B;D2:D})

Then to get the topic corresponding to a particular lecturer or assistant:

 =ArrayFormula(IFERROR(vlookup(filter(F2:F,F2:F<>"")&filter(G1:1,G1:1<>""),{B2:B&C2:C,A2:A},2,false))&
    IFERROR(vlookup(filter(F2:F,F2:F<>"")&filter(G1:1,G1:1<>""),{D2:D&C2:C,A2:A},2,false)))