Split rows into different sheets after blank rows

[hiteshnpwc]

Hello Everyone,

Wanted to know if there is any way that I can split data after the null row into multiple sheets.

Excel data had appended with headers from other files and there is a blank row, I want to separate those and create a union so that under single headers all the data would be appended and extra columns should be added at the deaders.

Please note that data has a different schema appended Has to try to multiple ways but did not get the actual output.

Thanks in advance..!

[alexnajm]

You could use a Multi-Row Formula tool to mark each of the different sections and be able to output them into different sheets. Something like this: Tool Mastery | Multi-Row Formula - Alteryx Community. Check under the "Create Unique ID for Each Person In Group" section.

However because you also want to union afterwards, I might suggest using a bunch of filters afterwards on the labeled rows and then Union them in Alteryx.

[hiteshnpwc]

@alexnajm 

Thank you for your Input. I have tried Multi row Tool, however, the data and number of rows might change every time. It should be dynamic and I was not able to configure it. Please let me know if I have missed anything while configuring it or an example workflow would be great to understand and learning aswell.

Thanks in advance.

[alexnajm · Accepted solution]

It doesn't matter if the data/number of rows changes - you can set it so that the number changes upon any null row. Something along the lines of IF [Row-1:Field1]=Null() THEN [Row-1:New Field]+1 ELSE [Row-1:New Field] ENDIF

[hiteshnpwc]

@alexnajm  Thank you. It worled.

[alexnajm]

Beautiful! 😊

[hiteshnpwc]

@alexnajm 

Hello Alex

Thank you for your suggestion. It helped me complete that step in building or completing that workflow. I thought to share the entire problem so that you help me with the possible solutions to complete the workflow.

I have an Excel file with multiple sheets of data (Different Schemas) I am trying to merge all the data sheets with single headers with extra columns.

Looking forward to your support, 

Attached my raw data and current workflow

[alexnajm]

Hi @hiteshnpwc,

I am sorry but I do not have the capacity to build out a workflow solution. I can suggest that if you have the rows properly labeled, you can use multiple filters one by one to get each group to one tool, then Union them all together. Based on the first file this should only be 6 Filters - you might need a Dynamic Rename after each one to Take Field Names from First Row of Data.

All of these would go into one Union. By default the Union tool will align based on column name - if it matches it will align but if it doesn't it will produce a new column to the right.

[hiteshnpwc]

@alexnajm  Thanks a lot, I will build the workflow accordingly and see the results