How to fix mis-alignment

Question

Hi,

Is there a tool in Alteryx that can help me to fix Excel misalignment? For instance, I have some columns with 2 values that I need to split but some have only 1 which is correct. And I need to shift one column one row up to match the rest of the data. I have no idea how to go about it. I'll take all suggestions.

The file comes from an external company so I cannot modify the format.

Thank you for your help.

AgnesL · Answer

@LindonB

I added a new tab called "Result".

Alteryx Sample.xlsx

LindonB · Answer

You probably do need a few tools, naming multi-row formulas to reshape the data, but it's certainly achievable. Can you share a table with your desired output?

LindonB · Answer

Depending on the shape of the data, you might need to use several tools. First, "Text to Columns" will allow you to delimit your data based on some character (split it out). If some cells don't have a delimiter, it will just skip over those. To shift columns up, you'd probably want to use a multi-row formula tool. In the expression, [Row+1:YOUR FIELD] will give you the next row's value (aka shift it up by one), but be caution of data groupings. Feel free to share some sample data and desired output if you'd like some specific help.