Partners,,,, russianxnxx

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

Separate Managers and Employees in different 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?



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.



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 


Topics that I write about...