21 Sep 2015

Have you ever received an awkward data set? In my life as a Data Analyst this has happened to me many times in different shapes and forms.

Cali_WP_

‘Video 00135 Cali Data Cleanup’ is actually based on a true story.

The real life data set was much larger so any hopes of manually copying and pasting the data are not possible.

 

 

Our goal is to make a Pivot Table to analyze Cali’s data. To do this, we’ll need to re-arrange this data set.

 

Cali’s Awkward Data Set

Cali Dataset

 

What’s The Problem?

Data set is re-arranged and pivot readyIn order to make a Pivot Table each different piece of data should have a column and each cell should only have 1 piece of data in it.

This screen shot shows us what this data should look like when we are finished.

 

 

 

 

 

How Can We Do This?

There are many ways to do this. If you had to repeat this task many times then you would want to automate this using VBA (Excel’s programming language).

In this case it’s a one time only emergency so we use a combination of tricks!

Read the overview below or watch the video for a detailed explanation of all of the steps.

 

Re-arrange Region Values into 1 Column

Region values into 1 columnWe need to take the horizontal Region values (‘North’, ‘East’, ‘South’, ‘West’) and re-arrange them to be vertical.

You could use formulas but I used the ‘ALT D P’ trick (run the data through a pivot table and extract the re-arranged data from the Pivot’s cached data).

This sample shows that we now have the Region values re-arranged vertically in ‘Column’ but notice that some rows have 2 lines of data (we’ll remove the blank rows later).

Look at rows 4, 10, and 13.

 

 

 

 

 

‘Text to Columns’ to Split Multiple Rows

Ctrl J

Rows that are like row 4, 10, 13, etc need to be split apart. We do this by highlighting column C and using Excel’s ‘Text to Columns’ feature.

For the ‘Delimiter’, we must use a carriage return (a carriage return is what put the rows in separate lines within a cell).

In the ‘Other’ box, we do this by holding down the ‘Ctrl’ button, pressing the letter J and then pressing ‘Next’ and ‘Finish’.

 

 

 

 

‘Alt D P’ Trick Again

After Ctrl J

Here you can see the result of the ‘Text to Columns’ step above.

I have concatenated ‘City’ and ‘Region’ into one column.

We will repeat the ‘Alt D P’ pivot cache trick to take two columns of data (‘Value’ and ‘Column1’) and put them into their own row.

 

 

 

 

 

A Few Final Touches!

almost done

  1. ‘Text to Columns’ on field ‘Value’ using – as a delimiter.
  2. Delete field ‘Column’
  3. ‘Text to Columns’ on field ‘Row’ using _ (Underscore) as a delimiter.
  4. Rename column headers as desired.

 

 

 

We Are Finished!

Data set is re-arranged and pivot ready

You can now select this data and create a Pivot Table to analyze who is selling the most ‘Liver Treats’ and who needs more ‘Grooming Kits’.

 

 

 

 

 

 

Download my Excel file

Download here or via my OneDrive (file 00135)

 

 

Watch My YouTube Video

 

Subscribe to my YouTube channel and learn more!

 

About Me

KevinInMunich_My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish, playing Chess and drawing nerds (it’s relaxing and gives me a break from data!)

Save

Save

Save

Save

Post a comment