Divide a sheet in an excel into two other sheets based on some criteria

[GouthamVijay]

Hey Folks,

I am very new to the Alteryx, Hoping to get a solution from the experts out here...

I have a columnA in fileA.xlsx , where i have many records in it. I have to cut the data from sheet1 and create two more sheets in a same file...

For Example

Before:

fileA.xlsx and sheet1

columnA

1

2

3

4

Part two

india

us

germany

Part three

asia

europe

africa

Expected Output should be:

sheet1

columnA

1

2

3

4

sheet2

Part two

india

us

germany

sheet3

Part three

asia

europe

africa

Please note that, i have to make this flow as dynamic, always "Part two" and "Part three" will be constant in the data rest all can vary.

Is there any way that i can divide into multiple streams of data by using any methods?

[messi007 · Accepted solution]

@GouthamVijay,

Please see below how you can do that : 

Attached the workflow,

Regards

[Ladarthure]

Hi @GouthamVijay, 

to do this, you can process in a few steps :

• first, use a formula to identify your different tabs with a formula like the one below to create a new field (let's call it tab name):

IF StartsWith([ColumnA], 'Part') then [ColumnA]

else null() endif

• this will give you the new headers from your data
• you can then use a multi-row formula (could be combined if you feel comfortable enough) to update the tab name :

if isnull([tab name]) then [row-1:tab name]

else [tab name] endif

• this will update the tab name for all the parts
• if you want then to rewrite in your file, what you could do would be using a block until done to be able to pause the process and have the file closed, and then in each output put a filter on the tab name and the output data
• another solution would be to still use a block until done, and then use the Take file/table name from field in the output data to have it a bit more automated

Don't forget in the output data to set it as overwrite sheet or range, if you set it to overwrite file, it will delete the file and won't write to a new tab!

[GouthamVijay]

Hey 

messi007,

Thanks for your response. Could you please attach the excel file which you used in the program.

Regards....

[GouthamVijay]

Hey @messi007 ,

Thanks for the quick response, Could you please attach the Excel file which you used in your program...

[messi007]

@GouthamVijay 

Here you go.

If the workflow solve the problem please mark it as accepted solution it will help others.

Regards,