Cross Tab Batch Macro

Question

Hi all,

Struggling to get my head around the best way to approach this one...

In the below example, I want to take a list of Categories from Table A, Filter and Cross tab Table B, to end up looking like Table C. It needs to be dynamic as the values are changing all the time.

I was thinking a Batch Macro but I have very little experience in making these. Maybe there is an easier way using specific tools? I'm looking to feed this as a set of lookup fields into an excel worksheet, hence the weird layout. I was thinking to use a Record ID tool and Generate Rows to find the maximum entries in an existing table then join using that?

Any help would be really appreciated. :)

danilang · Accepted Answer

It looks like there's no relation between the 2 category columns.  Your final result just has the 1st Category column prepended to the cross tab of the second.

If that's the case, use a multi row tool on the second table to create an ascending RowID grouped on Category.  Then CrossTab grouped by RowID using [Category] as Column headers and [Product] as the Values

Then use a join between the output of this and the 1st table by record position.  union the J, L and R outputs to handle any missing rows

Dan

KOBoyle · Accepted Answer

I don't think you need a macro. I agree with @danilang, it looks like the Category column output C looks unrelated and redundant to the cross tabbed columns, but if that is exactly what you need you could accomplish it with a workflow like this one.