How to properly transpose my data and combine like-columns?

[capnshanty]

I'm having trouble getting alteryx to flip my data in the correct way. When I apply the transform method, the end result looks way more like I used the "melt" command in R or something than it does a true transposition. Further, I also need to combine the duplicate columns where possible, but I'm not really sure how to do that. I have no control over how this excel sheet is given to me, by the way, so I'm fairly well stuck with the format.

I've attached a mockup of how it comes to me. You'll notice the green-text entries are columns, I need those to be the rows. But also, note that the current rows are repeating, stuff like "amortization" appears multiple times. Those all need to be combined. I have on the second sheet attached how it should wind up looking. Thus far, I have tried using the cross-tab and transpose tools in alteryx, but they... have failed, like I said. I'm not sure how to get the data to look like what I want it to.

[AbhilashR]

Hi @capnshanty, I am not sure if this is the solution you are looking for but giving it a try.

I converted values into numeric and then summarize the columns, to sum up, the Amortization value. The rest of the workflow merely uses transpose and crosstab to set up the data in the final format. I wasn't sure what date needs to be captured in the last column (Date) of the Desired End State tab. 

Let us know if this isn't what you are looking for.

[Blake · Accepted solution]

Hi @capnshanty, 

Check out the attached workflow and let me know if that is what you're trying to accomplish. 

Please note that Alteryx does not allow duplicate field names so those are serialized (e.g. billed, true-up,etc).