I saw Chandeep’s Data Cleanup Challenge and it looked like fun. Others solved it using Power Query (some amazing solutions) but I used good ol’ fashioned formulas!
We start with this:
and we need to re-arrange it into this:
Power Query or Formulas?
You’ll see amazing Power Query solutions in the comments section of Chandeep’s post plus a Dynamic Array solution! I solved it using traditional formulas.
Download my Excel file and follow along.
My Formula Solution
Step 1 – What’s the pattern?
Each group starts with a Manager Code, Manager name, followed by one or more Employee names.
This formula adds a position number at the beginning of each group:
Step 2 – List Start of Each Group
I used a counter and the small function =SMALL(Table1[Pos ‘#],D5) to get the position numbers (removing spaces as seen above).
Step 3 – Manager eCode
A simple Index function referencing Step 2 to create a vertical list of the Manager eCodes: =INDEX(Table1[Data],E5)
Step 4 – Manager & Employee Names
Get the names after the Manager eCode. Stop when the next group starts. I used a 2nd counter (column headers) and this formula to know when to stop extracting names:
The end result looks like this:
Here’s an older post where I unstacked data with the one and only Oz du Soleil!
About Chandeep Chhabra
Chandeep is from Gurgaon, India. Before venturing into the Excel training & consulting world he worked at an investment bank. His blog is https://www.goodly.co.in/
Check out Chandeep’s chart quiz game. I scored 6 out of 7 🙂
My name is Kevin Lehrbass. I live in Markham Ontario Canada.
Excel challenges are fun!
Do you have an interesting challenge?