Tranpose each column of data

Hi - Example report attached. I have a report where I would like to transpose each column and the respective data within that column. For example, in rows 8-12, I would like to transpose all of these to be their own Fields and then fill in those Fields with the data below it. Feel like this might be a combination of transpose and cross-tab tools but any help and example workflows would be greatly appreciated to get me started.

Might be easier to see example Input vs. Output expected.

Thanks!

Report transpose cross tab help.xlsx

Transformation

Accepted answers

estherb47

@cireost

Not so bad to reuse the tools if you only need the first four columns

StartProcess.yxzp

All comments

estherb47

Hi @cireost !

Here's where I would start. I prefer the Make Columns tool in this instance to the Crosstab The way it's used here is to make 5 columns (because the first column of data contains 5 fields)

Better yet would be to turn this into a macro that will process one column at a time, dynamically feeding the Make Columns tool with the number of columns needed (determined by the number of cells that aren't null in the first 6 rows).

Without the macro, just copy the process and repeat for each column needed. You'll adjust the Make Columns tool to have it make the correct number of columns. And then use a Join multiple, on position, to bring all of the data back together.

Cheers!

Esther

StartProcess.yxzp

estherb47

@cireost

Not so bad to reuse the tools if you only need the first four columns

StartProcess.yxzp

cireost

Thanks! This is awesome and a great start! This works as intended for the first four columns. However, looking at the next several columns E-G, I realize that I want to keep the orientation of those columns. Therefore, the details of the first four columns will be repeated up to however many data points there are in columns E-G. I understand this is probably confusing to read but I have attached an updated output and hopefully this will explain it.

Thanks again!

Report transpose cross tab helpv2.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703