Issue facing Crosstab

Hi everyone,

I am facing an issue with the input data. I am having a dataset in excel in following format

And I want to transpose the Month columns into rows and the dimension rows to columns. I have tried using transpose and Crosstab combination, however it is summing up all the records when I am using the last cross tab. I only want the positions to change and no calculations in the number part. Below is the screenshot of my workflow.

I am using the first Select tool, to select required columns, Transpose tool to extract sheet name which is to be used as Country name, data cleansing tool to remove whitespaces, transpose tool to Transpose the Months to rows and then Crosstab to move the dimensions from rows to columns.

And over here the numbers are being summed up

Transformation

Accepted answers

Kenda

Hello @Deba93

Are you looking to Transpose like in Excel? @patrick_digan created an awesome macro that can do this! I generated some sample data to show a simple example below. Hope this helps!

ExcelTranspose.yxmc

All comments

apathetichell

Can you post as data (ie .xlsx) in lieu of a screen grab? You probably need to choose a key column for your crosstab - that may prevent it trying to sum multiple occurrences depending upon how your data looks.

Kenda

Hello @Deba93

Are you looking to Transpose like in Excel? @patrick_digan created an awesome macro that can do this! I generated some sample data to show a simple example below. Hope this helps!

ExcelTranspose.yxmc

Deba93

Hi @Kenda ,

Exactly, I am looking for transpose similar to what we do in excel. Thanks a lot 🙂

DawnDuong

Hi @Deba93

Seems like we are in the same profession and have similar use cases.

Did you select the new “month” column as “key field”?

If you don’t, all the months will sum up.

in addition, does the original “dimension” column contain only unique values? If not, you need to add RecordID tool and create uniqe identifier for each record before the Tranpose tool.

dawn

Deba93

Hi @DawnDuong ,

Will try out your solution as well. Thank you for posting this, I did include month as key field

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703