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

Developer

Accepted answers

grazitti_sapna

HI @SureshBurra1982 ,give this workflow a try.

Thanks.

Create multiple sheets-V2-GS.yxmd

All comments

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

sheets.yxmd

Multiple Tabs.xlsx

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

sheets_1.yxmd

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

SplitFile.yxzp

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.

Create multiple sheets-GS.yxmd

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

HI @SureshBurra1982 ,give this workflow a try.

Thanks.

Create multiple sheets-V2-GS.yxmd

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.

sheets_2.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167