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!


Challenge Review

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:








More Unstacking

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 🙂


About Me

My name is Kevin Lehrbass. I live in Markham Ontario Canada.

Excel challenges are fun!

Do you have an interesting challenge?


Leave a Reply

Your email address will not be published. Required fields are marked *