Issue facing Crosstab

[Deba93]

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

[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 · Accepted solution]

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!

[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