Best way to create n columns of mmm-yy (Jan-18, Feb-18, Mar-18 etc.)

[JOliver251]

Hi,

What would be the best way to:

1) Create a column for the next say 24 months and name the columns in a format like mmm-yy (like Jan-18, Feb-18 etc.)

2) Create a annual subtotal for each row

I was thinking the answer for 1) would be some kind of iterative macro and 2) would be a use of the multi-row formula tool but I'm not sure how to achieve this.

Thanks in advance!

[LordNeilLord]

This is hard to advise without seeing the data

But i would first create your data in rows, format the date field to be the format you need, then crosstab that data to get everything back in columns

If you can give a sample I can offer more detailed advice

[mbarone]

Hard to say for sure without seeing your data, but I would suggest using a Generate Rows tool, in combination with a Cross Tab tool.  The Generate Rows tool will take you from:

to:

in one fell swoop.

configuration is:

Then cross tab it out, but I'm not sure what to choose for a value (you mention annual subtotals so I'm thinking there must be some kind of numeric value somewhere in your data).

For simplicity purposes, I created a value of "500" for each month, that looks like this:

Then connect it to a cross tab and configure it like this:

And your results will be:

You can use a dynamic rename tool to change your columns to the appropriate date format.

[RsRoel]

Not completely sure what you want without having data. But see the workflow below, generated 24 months with the Generate Rows tool. Assume you have some value to create the annual subtotals from, I used 100, followed by a running total to generate the subtotals.

[JOliver251 · Accepted solution]

Thanks that made a lot of sense, I had an issue with the cross tab once I'd converted into the date format I wanted (where the columns grouped as Jan-18, Jan-19, Feb-18, Feb-19) so I changed the flow around a bit. So I used your method to create a macro that appends these columns onto a dataset (possibly a niche requirement but suits my purpose right now!) - see attached

Regarding my question 2 I now release there is a CReW macro that does this! (Add Totals)

Thanks all for your help :)