Cross Tab Batch Macro

[JimBow]

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 solution]

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 solution]

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.

[JimBow]

Thanks Dan, that was a great help...

I was thinking there must be a way but hadn't considered the Multi-Row Tool. Had some fun getting the expression right but there now.

You're right about there being no relation in the C Table for Category. The layout I wanted is for a classic Cascading dropdown, so the Category column on the left most column acts as a dropdwon to find the next level dropdowns.. I plan to make a Legacy workbook, then insert this into some dynamically named ranges and it should be good. 

Thanks again

[JimBow]

Thanks KO,

Much appreciated example, very similar to where I got to from Dan's explanation... no doubt a lot slower. :)