unstacking-data-with-chandeep/

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

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.

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:

=IF(\$E5+G\$4>=\$E6,””,INDEX(Table1[Data],\$E5+G\$4))

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!

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 đź™‚