How to align columns in alteryx that are shifted within the same file?

[hydrogurl01]

Hi,

I'm trying to clean a few Alteryx files for preparation but am facing a dilemma when attempting to do so. Each file has multiple sections within it with headers repeated for each of those sections. However, I'm seeing that within one file there are some sections that are shifted a row or two over while others are not within that same file. So I'll have a Total column in column 'M' in the first couple hundred of the excel sheet, but from row 400-500, I'll have Total in column 'N' or 'O' within the same file. I have issues like this for each of the couple hundred files I'm trying to bring within the same workflow to clean these out. Is there a way to align the columns properly within Alteryx so I have all of my Total columns in one row?

I'm fairly new to Alteryx still so trying to learn the ins and outs. 

[jrgo]

@hydrogurl01,

Any chance of posting an sample Excel file that would represent what you're working with? This would eliminate the guess work and also potentially share a solution that would be easier to use with your actual data.

[hydrogurl01]

---

@jrgo wrote:

@hydrogurl01,

Any chance of posting an sample Excel file that would represent what you're working with? This would eliminate the guess work and also potentially share a solution that would be easier to use with your actual data.

---

Sorry for the late reply, I aggregated a few files, but some of them have additional columns than the others. I've attached a sample of it. Is there an easy way to clean this up that you may know of?

[Nurul]

Hi @hydrogurl01

Here I share my workflow that may help you to solve the problem, mybe you can get the idea from here.

[hydrogurl01]

Hi Nurul,

The workflow you've attached can't be opened in my Alteryx file because it is a newer version according to the error message. Do you know a way around this?

[Nurul · Accepted solution]

Hi @hydrogurl01

You need to upgrade your Alteryx Version. Here I attached the image of workflows. I use formula to shift data to the left column and I filter Subscription Date that Null because there has a few data that need to shifted to right and I use Parse tool to separate combination data in one cell and use again formula like previous.

I hope you understand and get the idea of my workflows.

[jrgo]

@hydrogurl01

Here's @Nurul solution that should open with version 10.5+

[hydrogurl01]

Sorry for the late reply! This worked perfectly. To fix the version issue, I edited the file in Notepad + and changed the version around. Thanks again!