Repeat data after Cross Tab

[skeen503979]

Hello and thank you for helping me through this formatting question.

I am trying to consolidate years of data into a single document I can easily search and reference.  When attempting to use the Union tool to compile months together the number of rows exceeds what Excel is capable of storing.  I would like to simplify down to just 2 data points (Serial number and date sold) so I will then know which month of data to query for full data.  I believe it should be simple with the Cross Tab tool, but this is a new tool for me and I'm not sure how to continue the data (column A, C, E, etc.)

Can you please tell me how to format my current data into the desired format below?

Thx!

Current format:

Desired format:

[BrandonB]

Maybe something like this? Workflow is attached.

 

[skeen503979]

@BrandonB thanks for replying.  Your solution isn't quite what I'm searching for.  I want to Join multiple files (which combined exceed 1 million rows) and produce 2 of those columns on a down then right direction if that makes sense.  I don't want the columns to display individual month's data (because the combined rows for Jan 2020, Jan 2019, etc. exceed Excel's limit).  I'm hoping to list as many serial numbers in one column as I can, then move to the right and continue producing data.

Current data 

Desired direction for producing data.

[Greg_Murray · Accepted solution]

@skeen503979 , 

Here is a workflow that might work for you. You can set your max row count in the formula tool and the macro breaks them up to columns that don't exceed that number. The macro uses a laboratory tool, so it may be a use at your own risk scenario, but I have used it before without issue. 

workflow 

macro

Hope that helps,

Greg

[BrandonB]

Maybe you can use a Join Multiple tool with the Join by Record Position option selected? You would feed all of your Input Data tools into this Join Multiple tool to achieve your result. 

https://help.alteryx.com/current/designer/join-multiple-tool

 

[skeen503979]

Thanks so much @Greg_Murray that looks great!  I'm running an older version of Alteryx and am not able to open your workflow.  Could you post a few screenshots of how those tools are configured?

[Greg_Murray]

@skeen503979 ,

You might be able to edit the version in the files to get them to open for you. See this link.

If that doesn't work let me know what version you're on and I'll upload another copy.

Greg

[skeen503979]

@Greg_Murray Sorry, I don't have the tools to edit that.  Here is the version I'm running.

[Greg_Murray]

Here you go. 

Greg

[skeen503979]

@Greg_Murray Thanks!  I was able to open those. Now I just need to learn Macros. 🙂 

Thanks so much for the help.