Repeat data after Cross Tab

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:

Help

Accepted answers

Greg_Murray

@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

create columns.yxzp

All comments

BrandonB

Maybe something like this? Workflow is attached.

crosstab.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335