I need help formatting this first data table into a similar format as the second table. Right now, each person's data is in its own column, but i need it all formatted as rows. I attached the workflow for reference. if there's any other tips you feel you can give me, please feel free. Thanks!
Hey @johnlooz
It looks like this may be a training assignment so I won't give the full answer, but I will point you in the right direction!
Transpose and Crosstab are your friends when it comes to restructuring data into a different format. Transpose is able to take a bunch of columns and make the data vertical
Crosstab is able to take values from a column and make them new headers
There is another step in between those that I will let you figure out on your own, but hopefully that helps get you started. There are a few different techniques that you can use to solve this, so I encourage you to get creative!
@BrandonB So the part i'm actually having a tough time with is the in between step haha. I know that transpose and cross tab will be used later down the line, but I can't figure out how to position the data to make it the right format for those tools to come in.
For cross tab to work it needs something to use as the column headers. You may see numbers as my column headers but these weren’t made using a record ID tool. You may want to try the multi-row tool to create some kind of record ID within groups.
Hi @johnlooz ,
There are a lot of missing columns from your data so we can't get to the desired output, but I assume that's secondary to getting the data into rows.
I've attached a workflow that does the transpose and crosstabs to get the data into row format.
I've attached a macro I used for grouping the ID field.
I hope this helps.
M.