Transposing Rows to Columns and their data

Hello,
I'm a first-time poster. I have a worksheet which has data in rows (with repeating Row Headers) and I'm trying to make it columnar instead.

I tried the Copy/Paste Special (Transpose) but it's a tedious manual process and the data will change from time to time.

Here's a sample of the 4 rows which repeat over and over. I'm also attaching a spreadsheet as well.

I tried nested formulas too. I simply can't get the desired result of having column headings and correct data underneath!
Any help is appreciated!

Regards,
Scott

Employee Bob
Supervisor Jack
Business HR
Function Benefits
Employee Nancy
Supervisor Cheryl
Business IT
Function Networking