Need assistance formatting data table

[johnlooz]

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!

[BrandonB]

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!

[johnlooz]

@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. 

[BrandonB]

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.

[mceleavey]

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.