Separate Managers and Employee Names in Columns – Data Clean up Challenge

Today a friend called for a tricky question! (they only call when stuck with an Excel / Power BI problem) 🙁

Nevertheless here is what he asked

The Question:

Given is a single columnar data for Manager ECode, Manager Name and Employee Names, which needs to be separated out into columns

Additional Info

1. The Alphanumeric (ending with a number) is the EMP code of the Manager
2. The next row is the Name of the Manager
3. The following rows are the reporting employees for that manager until you reach the next Alphanumeric

Result Expected:

You can see that the Manager Details (ECode and Name) and Employee Names are placed in different columns

Ready to give it a shot?

DOWNLOAD THE RAW DATA

Please post your answers in the comments

1. Feel free to solve it the way you like – Excel Formulas, VBA and Power Query would be awesome!
2. Please drop a link for downloading your solution file.

Cheers

Hungry for a few more challenges?

1. Find the Unique Dates – Challenge
2. Convert Multiple Columns in Rows – Solved
3. Ranking based on 2 conditions
4. Separate the Values in Columns