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

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

Part two

india

germany

Part three

asia

europe

africa

Expected Output should be:

sheet1

columnA

sheet2

Part two

india

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?

Developer

Designer Cloud

Regex

Data Investigation

Accepted answers

messi007

@GouthamVijay,

Please see below how you can do that :

Attached the workflow,

Regards

Prime Analytics loves Alteryx.png

SplitExcel.yxmd

All comments

messi007

@GouthamVijay,

Please see below how you can do that :

Attached the workflow,

Regards

Prime Analytics loves Alteryx.png

SplitExcel.yxmd

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...

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703