Split the data using two column and combine

[Jwwwson]

Hi all, 

Please advise if how I should do if I would like to split the data based on the 1st and 2nd name and group according to the names into different excel files, with results as shown below?

Samples:

1st Name	2nd Name	Quantity sold	Cost per unit	City
Wilson	Sandra	2	500000	Newyork
Wilson	Peter	4	600000	Seattle
Peter	NA	6	1000000	Chicago
Drusilla	NA	1	200000	Seattle

expected results:

excel 1:

1st Name	2nd Name	Quantity sold	Cost per unit	City
Wilson	Sandra	2	500000	Newyork
Wilson	Peter	4	600000	Seattle

excel 2: 

1st Name	2nd Name	Quantity sold	Cost per unit	City
Peter	NA	6	1000000	Chicago
Wilson	Peter	4	600000	Seattle

excel 3:

1st Name	2nd Name	Quantity sold	Cost per unit	City
Wilson	Sandra	2	500000	Newyork

Cheers, 
Jason

[AbhilashR]

Hi @Jwwwson, what is the logic to group the records across multiple files? For now I have put together a sample for your reference.

Please mark this post as a solution accepted if it answers your question, or let us know if this isn't what you are looking for.

[grossal]

Hi @Jwwwson,

what is your criteria for the split? As soon as you have defined a splitting-criteria column you could use the advanced output tool options: 

Let's save your splitting criteria is the first name - therefore we'd create a new file path based on it:

We than change the output to take this field into consideration:

The output looks like this:

I have attached my sample workflow. Let us know what the splitting criteria is and we can figure it out. Feel free to test it yourself in the attached example. Maybe splitting it up into cities would be a good idea? What do you think? If you want it to be split up in cities, just change the [1st Name] Field in the formula to City and you should be good to go.😃

Best

Alex

[Jwwwson]

Hi @AbhilashR 

Actually I have thousands of samples in the data, and first I would like to group the samples by certain columns, say 1st name and 2nd name in this example, and then split the data by name into various excel or tabs. 

Cheers, 
Jason

[Jwwwson]

Hi @grossal , 

It works! Thanks!

What if I would like to group the data taken into consideration of 1st and 2nd name, say Peter in the sample data, which appears in 1st name in one sample row and 2nd name in another row, but I would like to include all the samples relevant to Peter into the 'Peter' excel, as shown below:

1st Name	2nd Name	Quantity sold	Cost per unit	City
Peter	NA	6	1000000	Chicago
Wilson	Peter	4	600000	Seattle

Any advice on how to define a splitting-criteria column or shall I do it twice, for 1st and 2nd name each and use union to combine?

Cheers, 
jason

[grossal · Accepted solution]

I'd probably go the way you suggested using a Union Tool and a Formula: 

I also added a select Tool in the end to remove the helping column from the output. Updated workflow attached. Would this solve it for you?

Best

Alex