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:

=IF(ISNUMBER(RIGHT([@Data],1)+0),ROW([@Data])-ROW(Table1[[#Headers],[Data]]),””)

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!

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?