how to transpose or cross tab for every sheet.

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.

example.xlsx

Workflow

Date Time

Transformation

Accepted answers

danilang

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

TransposeAllSheets.yxzp

All comments

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

PivotColumnsRows.yxmd

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.

example.xlsx

ponraj

Here is the sample workflow. Hope this is helpful.

how to transpose or cross tab for every sheet..yxzp

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703