Adjusting rows of data to columns to prep data

[ameynder]

I am new to the alteryx discussion boards.  I am trying to adjust data.  I have seen other question/answers that solve a portion of my issue, but not completely.

I am trying to get data like this:

  

To look like this:

Thank you for helping

[JosephSerpis]

Hi @ameynder I mocked up a workflow that I think answers your question. I have commented my workflow as much as possible to explain what I have done. Are you able to provide any sample excel in order to test? 

[ameynder]

Here is the sample in excel.

[JosephSerpis]

Hi @ameynder I used your sample data and seems to produce the output you describe. Let me know what you think?

[ameynder]

Hi @JS420, I am working to get a more robust data set for you. I will post it soon. It will help test the workflow on more realistic data.

[estherb47]

Hi @ameynder 

Here's a solution that uses mostly the Favorites tools.

Basic logic here is to separate out the multiple column headers, and then join the data back together. The first row grabs the Area headings, transposes them to rows, and fills in blanks with a Multi Row formula tool. Then I added an Area Order field to help with sorting later.

Next grab the Function headings (the second Select records tool). Transpose again, and add a field for ordering.

Last Select Records grabs the rest of the data. The transpose tool on the rows above has a simple F3-F11 schema on the Names column. We'll need the same to match up the rows back together. So, a little adjusting first. Add the column name to the value in columns F3-F11 with a Multi Field formula tool. Then use Dynamic rename to pop the column headers into the column headers row. Transpose to make the data vertical, as with the two top streams in the workflow. Next we need to pull out the F3 - F11 for the Join Multiple, so we can use Regex_Replace. Finally a simple Replace formula fixes the Name and Value columns to remove the F3-F11 headers.

The one piece that needs adjustments with your data will be the Text Input tool. There, you can type in the individual Measures and the order by which they should be sorted.

A Join Multiple tool rebuilds the rows, by matching up the F3, F4, F5, etc.

Then a sort to reorder the rows, and a Select to reorder the columns.

Let me know if that helps!

Cheers,

Esther

[ameynder]

Here is the more complete data set.  I have gotten close with what has already been posted, but I am struggling getting everything to come together.

[ameynder]

@EstherB47,

I was able to get very close with your workflow, but I can't get all the way there.  I have fuller data set.  Let me know your thoughts

[estherb47]

Hi @ameynder 

Are you able to either post the data itself, or export your workflow as a yxzp (options menu, export workflow, then attach the yxzp file to a response here)

I can't troubleshoot without actually seeing the data, unfortunately.

Cheers!

Esther

[ameynder]

I thought I had posted the data.  Let me know if this works.