SIMPLE TRANSPOSE, HOW IS IT DONE?

andrebrandaobritob

I'm having a lot of trouble trying to do a simple transpose like we do in excel.

My worksheet is like the image 1.jpg, I need to do a simple transpose so it shows like the image 2.jpg.

Can you guys help me accomplish that? I attached the sample excel file.

Thank you!

Sample.xlsx

1.jpg

2.jpg

Transformation

Accepted answers

alexnajm

Hey @jbichachi003 - can you clarify the need for the Transpose after? I tested this quickly and I don't think it's necessary

More can be done to get the column order/names right, but this is the main drift (didn't want to have to build it but here we are haha)

Simple Transpose.yxzp

All comments

alexnajm

Transpose to move your headers to a column, and THEN Crosstab to move your [Parcela] column to headers!

TUSHAR050392

@andrebrandaobritob Another approach which involves multiple use of crosstab tool since it can let you move only 1 column at a time. Use the cross tab tool, select Column A in change column headers, then column 1 as Values for new columns. This needs to be done five time and Column A will remain constant always with Column2,3,4,5. Once this is done, you can union the data from all 5 crosstab tools to get the output.

Hope this helps

jbichachi003

@TUSHAR050392 will work, but @alexnajm's solution is the least manual (especially if the final set has a lot of columns). As @alexnajm said, you'll need to do a Transpose followed by a Cross Tab. What was left unmentioned is you'll need another Transpose after the Cross Tab.

A couple of things to note:

The default configurations for the two Transposes and the Cross Tab can be left as they are.
Once you finish the transformations, you can use a Text to Columns tool to split out the number of columns you'll need (this is manual and if the final data set has more columns you'll need to update this tool).
Keep in mind--because your data contains null values, the combination of the two Transposes, Cross Tab, and Text to Columns will yield incorrect results (you'll find the some values shifted over to incorrect columns). To correct this, use a Multi-Field Formula before the first Transpose to replace all Empty values with some unique combination of letters/numbers/characters. Once all the transformations are complete, you'll undo this with another Multi-Field formula.

Best of luck!

alexnajm

Hey @jbichachi003 - can you clarify the need for the Transpose after? I tested this quickly and I don't think it's necessary

More can be done to get the column order/names right, but this is the main drift (didn't want to have to build it but here we are haha)

Simple Transpose.yxzp

jbichachi003

Hi @alexnajm, I'm glad I posted because I enjoyed seeing your solution to it without the need for the Text to Columns tool. Here was my solution, which is similar to yours, but with the unfortunate use of the Text to Columns tool and the need to use the Multi-Field tool. Unlike your solution, I don't configure the Transpose or the Cross Tab tools.

That said, your solution is more elegant, especially if order is not relevant.

Pivot.yxzp

CoG

Here is my approach to solving this problem: https://youtu.be/lTrCEbDccb4 (same premise from another recent question)

Also, here is the Custom Macro Tool I built for this purpose: Flip Tool - Alteryx Community

The macro should handle all of the functionality you are looking for and takes into account row and column order + column naming!

Hope this helps and Happy Solving!

Qiu

@CoG
Nice to see you have a YouTube Channel, but I was wondering how did you change your Alteryx Community User name😁

CoG

Thanks, @Qiu! I figured it would be a good way to provide more detail to help others learn.

Username Change: Mission Control (alteryx.com)

Then Edit "My Profile" from Preference Center (Took me longer than I'd like to admit to figure that out)

Qiu

@CoG
Thanks for sharing how can we change the user name.

alexnajm

Interesting @jbichachi003 - I like your workaround to those annoying Crosstab problems!

andrebrandaobritob

thank you all for your inputs! 😁 Problem solved!

jbichachi003

Hi @CoG, cool macro. I happened to also create a macro for fun which does this very thing. Unlike your macro, mine is iterative, handling each row one at a time, pivoting it, and then outputting it before going on to the next row. I also included the CReW Expect Equal macro into this workflow (not the iterative macro) to test the results against the Excel version. The only extra thing I needed was a Select tool to update the data types and remove the "Original Column Name" field.

This macro assumes the first column is the column that contains the new desired column names and the starting column headers cannot contain any data that will need to be pivoted.

Pivot.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703