Design Pattern – Misaligned and merged cells

Excel! 

This isn’t a dig at Excel, as it is really useful, but when the priority of the spreadsheet is prettiness it can be a pain for a data analyst! One common problem is users using merged cells to make the input in Excel appear aesthetically pleasing, but when this gets input into Excel it needs some clean up before you can extract insights.

This design pattern is focused on how to deal with misaligned merged cells. So the issue is when you have merged cells that look like this:

When the data is read into software such as Alteryx, the data is actually only in one cell (hence field) and it will default to the left most, so for our data Customer name is in column B, Account number is in column C etc. The result in Alteryx is the following:

So we need a way to bring together (concatenate) all of the data in each row, and then to split it using the : delimiter. So this design pattern below is required to clean up the Excel input.