Rescue oddly shaped data – Battle between Formulas, VBA and Power Query
This is inspired from a forum question by Chanthan about messy data.
Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.
How do you reshape it to one column?
You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.
Oddly shaped data vs. Formulas
ln response to Chantan’s question, Narayan, our forum ninja posted this beautiful, complex and almost cryptic formula.
=IFERROR(INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(myrange <> "", ROW(myrange) + COLUMN(myrange)*0.00001), ROWS(A$1:A1)), "00000.00000"), ".", "C"), FALSE), "")
It is a bit too much to explain. But I will give it a go:
- We want to find out the address of the cells that have some value in them.
- Start by finding the row & column numbers of cells that are not empty – myrange<>””
- Add these two to generate a decimal number in the format row.column*0.00001
- Reformat this in 00000.00000 format using TEXT
- Replace decimal point with C and prefix R, so you get R00000C00000, a la R1C1 notation
- Use INDIRECT to get the corresponding cell value.
My verdict: Works beautifully, but you need serious Excel skills to either write it or change it. Also, volatile as it uses INDIRECT.
Oddly shaped data vs. VBA Macros
VBA is so easy for things like this. You can just iterate thru myrange and copy non-blank values. Here is a simple VBA macro to do that. It will paste output from a cell named paste.here.
Sub extract() Dim cell As Range, i As Long For Each cell In Range("myrange").SpecialCells(xlCellTypeConstants) Range("paste.here").Offset(i).Value = cell.Value i = i + 1 Next cell End Sub
My verdict: Works like a charm. You just need entry level VBA skills to understand and use this. But requires re-running when data changes and permissions.
Oddly shaped data vs. Power Query
Power Query is designed to mitigate pains like this. You just have to load the data to PQ and give it a good scrubbing to get shiny extract in a single row.
- Load myrange to Power Query (use Data > From Table or Power Query > From Range)
- Select all columns, right click and choose Merge Columns with separator as comma (or any other symbol not present in your data)
- Right click on the newly created column and split it by comma in to rows.
- Filter away null values
- Close and load the data.
My verdict: If you have Power Query (or Excel 2016) and not using it to slap some sense in to shapeless data, then you are punishing yourself. Power Query is for things like this people. So use it with abandon.
Download oddly shaped data – done three ways
Click here to download the oddly shaped data workbook. Play with the formulas, VBA and PQ to learn each better.
Improve your career odds – learn Excel
If you had enough of being the odd person out due to your pear shaped Excel skills, then its time you got in shape. Start with these free resources.
What is your verdict?
What do you think about these three techniques? Which one is better according to you? Please share your thoughts in the comments section.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Maths worksheets for your kids – teach addition, subtraction & multiplication with these||Visualizing Commonwealth games performance – Interactive chart »|