Splitting Excel based on one common keyword which is available in one coulmn.

[SureshBurra1982]

Hi All,

I have one excel which consist of huge data in it. I have to split the excel file into multiple excel files based on one keyword which is available in one single column and multiple places through out the file.

Example:

Lets say I have 100 rows and 4 columns. Keyword is available in 1st column in different places like 10,20,30,40,70,100 rows respectively. Whatever the data from 1 to 10 rows will be first excel, 11 to 20 rows will be second excel, 21 to 30 rows will be 3rd excel, 31 to 40 in one, 41 to 70 in one and finally  71 to 100 in final excel.

Thanks

Suresh

[OllieClarke]

Hey @SureshBurra1982 you can use a multi-row formula to create a new field called 'sheet' which will add 1 everytime the keyword appears. You can then use this field to change the table/sheet when outputting to a .xlsx

Hope that helps, Ollie

[OllieClarke]

@SureshBurra1982 Sorry, just saw you want different excel files. In that case you'll need to create a filepath using a formula tool. In my case I've called my excels just the sheet number, but you could edit this name to whatever you want.Make sure you include a sheet name preceded by ||| though. You'll then need to 'Change Entire File Path', and you should be golden.

Hope that helps, Ollie

[RolandSchubert]

Hi @SureshBurra1982 ,

I think, a Multi-Row Formula too may be used to create a field containing different file/sheet names. You can write to different files using the output tool.

I've attached a sample workflow. Let me know if it works for you.

Best,

Roland

[Qiu]

@OllieClarke 
just about to post the minior correction, and you did it first.

[grazitti_sapna]

Hi @SureshBurra1982 , my workflow is similar to @OllieClarke  just replaced field="keywords" with contains (field,"Keywords").

I hope it helps.

Thanks.

[SureshBurra1982]

Column1	Column2	Column3	Column4	Column5
ID :1
	ABC	ABCD	XYZ	AA
	ABC	ABCD	XYZ	AA
	Completed
ID :2
	XYZ	ASDF	AVCD	ASD
	Completed
ID :3
	ASZX	AWER	AF	AXGD
	Completed
ID :4
	ASD	ADDF	DFF	ASW
	AZS	AWS	ASD	SAP
	Completed

Based on Completed keyword, I need to split the main file into excels and name with respected ID. Ex. 1.xlsx

The information from starting to till first Completed word will be in 1.xlsx and 1st Completed to 2 nd Completed will be in 2.xlsx and so on.

Could you please help me on this?

[grazitti_sapna · Accepted solution]

HI @SureshBurra1982 ,give this workflow a try.

Thanks.

[OllieClarke]

Hey @SureshBurra1982 all you need to do is update the multi-row formula as below (I also added a data cleansing tool to get rid of trailing whitespace). The actual process is identical to my last post.