Consolidate Names/Department into 2 Columns

I have a workbook that is periodically updated containing department codes followed by a list of names assigned to the department.

The department codes are in Row 1 & Row 21 spanning columns A:G.

A2:G19 contains the names under the department codes in Row 1

A22:D39 contains the names under department codes in Row 21.

The desired output is in a Table in I1:J159.

I can't, for the life of me, figure out a good way to automate the output illustrated in the table.

Any help is greatly appreciated!

Welcome to the forum.

Here's a simple solution using PowerQuery:

1. Load each of the source tables into PQ (uncheck My table has headers in each case). Transpose and then unpivot all but the first column, then delete the middle column. Close and load to connection.
2. Create an append query from the two tables - relabel the column headers, sort and then close and load to your desired position.

If the source tables change, just refresh all queries (Data ribbon).

No problem.