In the movie Forrest Gump I’m sure you remember when Bubba lists different ways to prepare shrimp: “You can barbecue it, boil it, broil it, bake it, saute it. Dey’s uh, shrimp-kabobs, shrimp creole, shrimp gumbo. Pan fried, deep fried, stir-fried. There’s pineapple shrimp, lemon shrimp, coconut shrimp, pepper shrimp, shrimp soup, shrimp stew, shrimp salad, shrimp and potatoes, shrimp burger, shrimp sandwich. That- that’s about it.“
What is Unstacking Data About?
Two or more columns of data are jammed together into only one column. It’s easier to analyze the data if we re-arrange it into an easier layout.
How Does Unstacking Data Relate To Cooking Shrimp?
Think of the single column of data as the shrimp. The cooking method is how we unstack it. There are many ways data can be stacked and various solutions for each case. How did I make this connection? I guess it was because there’s so many different ways to stack & unstack data:
“It can have bold text, underlined text, text with extra spaces, text with different colors, indented text, text with special characters, text with a prefix…There’s a vba solution, vba with formulas, find feature with a filter and formulas, find & replace with formulas and Get & Transform…normalized output, pivoted output…it goes on and on!”
By the way, I’m not saying that I’m Forrest and Oz is Bubba but that would be kind of funny!
Challenge from Oz du Soleil
Requirement: The single column below on the left lists parents in bold with children beneath in non bold. We need to rearrange it as seen on the right.
Who Else Is Unstacking Data?
It seems that all the cool kids at school are doing it!
Ken Puls recently published this blog post about unstacking data using Get & Transform.
Mike Girvin heard about Oz’s challenge and created a video solving it with get.cell (retro style macro)!
Chandoo joined the unstack party with this post about unstacking manager/employee data.
More Stacked Data Examples
There’s an infinite amount of ways to stack & unstack data. Download these examples: Excel file.
Can you solve them?
My name is Kevin Lehrbass. 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 dogs Cali (she doesn’t like Excel) & Fenton, learning Spanish and playing Chess.