Transposing and crosstab

Question

Hi All,

I have a transposing issue in a dataset that I have downloaded from a source. This needs to be refreshed every year.

For better understanding of the situation I have attached the input and the output example.

Is it possible to have a look and help on this.

Thanks in anticipation.

Regards

G

Data Financial Input.xlsx

Data Financial Output.xlsx

Data Financial Output - 2.xlsx

SeanAdams · Answer

Hey @gmamtani

a few things that may help:

Firstly - here's an article that may help you in structuring discussion threads to make it quickest for people to help you solve:

Get Answers FAST! (alteryx.com)

The other one is to help you to get your head aroudn transpose and CrossTab.

- Transpose's job is to take things from columns to name-value pairs.    so if you have a row with an ID, PersonName; Height; HomeAddress - you can transform this by Transpose to a list down the page where each row just has 3 columns - ID; Name; Value.   In this context name is the name of the field; and value is the value in the cell.    Think of this as making a cell-list of all the cells in a row.

- Crosstab goes the other way, and works like an excel pivot table.    So you are going from a list of attributes down the page to a list of fields across the page.    So - in our example above - we would take ID; Name; Value - and crosstab this with ID being the Key; name being the field name; and value being the cell value.

So if you have a bunch of different data with a shared key:

- Transpose them all with that key so that you get the 3 columns

- Union these lists of attributes (name-value pairs) together

- and then cross-tab (or pivot) them back out to be columns on the page.

It took me a while to get my head around Cross-tab and transpose, but once it clicks it will stay with you forever.

gmamtani · Answer

Sorry, I did not get you.

The logic is to normalize the data and get a flat file. Right now the raw data is difficult for modeling.

My understanding is that transpose and crosstab would work. However, I am very new to these macros, so I thought I can get some ideas from the community.

Raj · Answer

can you please elaborate the logic you want to apply