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.
boom.

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.