21 Mar 2019

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?

 

Post a comment