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?

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 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI