Split excel data based on a field, add headers and export as separate files

Hi,

I have a table with headers, i want to split the data based on "Car" field and export those set of rows to different files. Before exporting these files i want the first three rows (in red) to be static in each file that I export. how to do this? i dont know macros or other programming languages.

Data base name: xyz
Sales Data
Car	Quantity sold	Cost per unit	City
VW	2	500000	Newyork
VW	4	600000	Seattle
MG	6	1000000	Chicago
MG	1	200000	Seattle

Common Use Cases

Accepted answers

wdavis

Hi @BalajiMRB

Would it work if you had the Database name and Sales Data information stored in fields?

If so, then the following workflow should work for you. By splitting the data out by the first 2 rows (defined by Null) you can then append them on to your entire data set with new fields.

Then in your final output tool, we have checked the box 'Take File/Table Name from Field' and modified this based on the 'Car' Field. This creates a separate sheet for every different car manufacturer.

Let me know if this works for you!

Kind Regards

Will

SplitData.yxmd

RolandSchubert

Hi @BalajiMRB ,

you can split the first 3 row using the SAMPLE tool, create a list of cars using the UNIQUE tool, generate the header rows for each car type by using APPEND ROW (append cars to header generates headers for each car), then union the headers and the data and output by a car. See attached workflow.

Best regards

Roland

SplitByCar.yxmd

All comments

wdavis

Hi @BalajiMRB

Would it work if you had the Database name and Sales Data information stored in fields?

If so, then the following workflow should work for you. By splitting the data out by the first 2 rows (defined by Null) you can then append them on to your entire data set with new fields.

Let me know if this works for you!

Kind Regards

Will

SplitData.yxmd

RolandSchubert

Hi @BalajiMRB ,

Best regards

Roland

SplitByCar.yxmd

BalajiMRB

Hi Davis,

Thanks for the response. Your solution provides two additional fields with required data (which is right in most of the ways), but my request is more focused on the format of the output. I want the first three rows in the output to be exactly same as the input. This is because the end users use these files as input into a different tool which processes these files and the format cannot be changed.

Jwwwson

Hi @RolandSchubert ,

Could you kindly advise how to output the data into different excel files, using your workflow?

Cheers,

Jason

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703