Rearranging data is initially about recognizing the pattern. This screenshot is from Ken Puls. I instantly saw the steps to rearrange the data (thanks to Ken’s online course!)
High level: (a) split ‘Clients Assigned’ names into columns, (b) unpivot those columns into rows.
This task is now incredibly easy thanks to Excel’s Power Query (Get & Transform) tool.
Get my Excel file and follow along.
- Select any cell within your data
- On the ribbon click ‘Data’, in ‘Get & Transform Data’ section select ‘From Table/Range’
- Right click column header ‘Clients Assigned’, ‘Split column’, ‘By Delimiter’, select ‘Comma’, split at ‘Each occurence…’
- Right click column header ‘SalesPerson’ and ‘Unpivot other columns’
- Right click column header ‘Attribute’ and ‘Remove’
- Double left click column header to rename ‘Value’ to ‘Clients Assigned’
Export Back To Sheet
- Click ‘Close & Load’, ‘Close & Load To…’, and select a location.
Why Rearrange Data?
Building a model on top of database layout data is much easier. However, there are some rare exceptions. If the task were simply to count the clients assigned to each salesperson use this formula:
Or if it’s a table format use this formula:
=LEN([@[Clients Assigned]])-LEN(SUBSTITUTE([@[Clients Assigned]],”,”,””))
Before Power Query
In the ol’ days before power query rearranging data was often painful. It’s possible to use formulas to solve this but it’s so much work. There’s also a trick to combine Text to Columns with the Alt D P pivot trick. Just a few steps but Power Query is still easier.
My name is Kevin Lehrbass. I’ve worked as a Data Analyst since 2001.
Power Query is a revolutionary tool! The basics are easy to learn but mastering advanced techniques takes practice. The vast majority of Excel users have no idea how valuable Power Query is.
Power Query is free! Starting with Excel 2016 it’s a built-in feature. Learn with me at Power Query academy.