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!
Solved! Go to Solution.
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
@cireost
Not so bad to reuse the tools if you only need the first four columns :)
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!
@estherb47 let me know if you can continue to help! thanks!