How to find multiple subheaders and convert each to columns for rows below

[rdphillips]

I've attached an excel file with 2 sheets.

The Sample sheet contains an example of scrubbed data (trimmed heavily), in rows 2, 6, 10, 14 are all subheaders. The subheader only applies to the rows below the subheader and above the following subheader. For example subheader in row 2, only applies to rows 3-5. I'm trying to find a way to move these subheaders into a column for the applicable rows.

The Target sheet, shows what I am I'm looking for as an end result.

I'm relatively new to Alteryx, so this type of data manipulation I haven't been able to figure out. I've only been successful moving Subheader1 to a column and applying it to the entire dataset - so not quite what is needed. Appreciate any assistance.

[aatalai]

Is there an easy way to identify the rows that relate to them? If so use a formula tool to tag them and then use the cross tab tool with the new tag column being the names you get the column from

[Raj · Accepted solution]

@rdphillips Find the solution  attachment.

[DataNath · Accepted solution]

Hey @rdphillips we can achieve this with a couple of tools. We do this by:

1) Using a Multi-Row Formula to pull out rows containing header values and filling them down

2) Filtering out rows with null values where we'd expect actual results

3) Rearranging to get your desired format

Hope this helps!

[rdphillips]

Rows that correspond to the subheaders are those rows between them. So in the example case attached subheader1 applies to rows 3-5, subheader2 applies to rows 7-9, subheader3 applies to rows 11-13, and subheader 4 applies to rows 15-17. The caveat to the real data, subheader1 will always be on row 2, but the other subheaders could be anywhere in the data depending on the activity for these groups. 

In other words in the full dataset, in month one subheader1 could apply to row 3-17 and subheader2 to rows 19-123, and in month two subheader1 could apply to rows 3-82 and subheader 2 to rows 84-99.

[JS1989 · Accepted solution]

Hope this helps:)

[rdphillips]

Thank you @raj, @DataNash, and @JS1989 with these slightly different solutions I can see how the different pieces are utilized - very valuable. Appreciate the quick response.

[AmitMiller · Accepted solution]

Hi @rdphillips , here's a possible way of doing this:

Regarding the way of thinking - the first part would be how to move the group values to a new column. Usually, the Formula tool gets the job done via an IF condition

Once you get that - you need to be familiar with window functions which are used via the multi-row formula tool. See documentation here

This tool can be used for a lot of scenarios, and it's worth looking into its one-tool example as well!

Hope this helps =] see workflow attached