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

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.

SubheaderQuestion.xlsx

Datasets

Transformation

Data Investigation

Accepted answers

Raj

@rdphillips Find the solution attachment.

751.yxmd

DataNath

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!

Reformat staged subheaders.yxmd

JS1989

Hope this helps:)

Subheader.yxmd

AmitMiller

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

Screenshot 2024-02-13 at 15.06.14.png

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

SubheaderGroups.yxmd

All comments

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

@rdphillips Find the solution attachment.

751.yxmd

DataNath

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!

Reformat staged subheaders.yxmd

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

Hope this helps:)

Subheader.yxmd

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

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

Screenshot 2024-02-13 at 15.06.14.png

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

SubheaderGroups.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703