how to transpose or cross tab for every sheet.

[luo]

Hi Guys,

I am working on a project. I have reached a final stage. Now I have a data table looks like this:

After this, I use output data tool spilt the data based on month into several sheets and an example sheet will be similar to the below with sheet name has been named as "Month, YYYY"

But I will need something like this:

Now I know I can get total with sum and I know how to do this if I have only one sheet. But I have multiple sheets and of course, if there is other way that can process the data before I use output data tool spliting the sheet, that would be good too. Thanks a lot guys. I have also upload the these sheet for your information. 

[RolandSchubert]

Hi @luo ,

you'll need Transpose tool, Cross Tab tool and a Dynamic Rename. I've attached a sample workflow. The Select tool is only needed to change the field for the width of the row labels  in your sample (a, b, c width 1) , so that the "Total" row can be named. 

Let me know if it works for you.

Best,

Roland

[luo]

Hi @RolandSchubert 

Sorry for not making it more clear. Thank you for the workflow. I got the idea to do this on one sheet. 

I will need do this on multiple sheets.

I have the data table like this:

And then I use "Output Data" Tool to output this sheet into several sheets based on the month. Like these two sheets below:

AND

I will need to do the operation on both sheets. But I cannot do it one by one because there maybe dozens of sheets to process. Is there any way to do this? Thank you again for your time. I have attached this excel also. 

[ponraj]

Here is the sample workflow. Hope this is helpful. 

[luo]

Hi, ponraj,

This is helpful. But can you tell me what's the meaning of your multiple formular? I saw that there is only two group. Can I Group By "Month"?

[luo]

and also forgive me, why are you using the macro? why not just use the workflow? bring all the tool outside? Could you help me to understand this?

[danilang · Accepted solution]

Hi @luo 

Here's another example of how to do this.  It's dynamic in that It will work for any number of months, outputting each of them into its own tab with the monthly total rows 

The workflow builds up all the required rows including the headers as a row.  There are two different output methods.  The one that's in the main workflow simply outputs the the result as is without writing the real headers.  This gives you a file where all the values are written as strings like this.  Note the Green triangles in the number cells

The second output uses a batch macro to change the header names to the take the first row of data.  This removes the day names from the columns, so it then uses an auto field to convert the "day" columns to integers.  This needs to be done in the macro, because we need to isolate each months data so the rename will work properly.  The output from this has the cells formatted properly as opposed to all being strings

Dan