Split output to multiple sheets based on a column value and the number of rows

[luaimatari7]

Hello All,

I have been stuck on this issue for quite sometime now, so it would be really great if someone could help me with this.

What I'm trying to achieve is a bit hard to explain so please have a look at the sample input and desired output below.  

Sample Input:

Period	Line Description
May	xyz
May	xyz
May	abc
May	abc
May	abc
May	yyy
May	yyy
June	xyz
June	xyz
June	abc
June	bbb
June	xxx

What I'm trying to do is to split this into multiple sheets in the output file based on the "period" column. I have done this using the output tool "Change file/table name" option and then I chose the period as the field. This is working fine, however I need to limit the number of rows per each sheet, for the sake of the example lets say that the number of rows allowed in each sheet is 4. The extra twist that I need to make sure is implemented is that rows with the same line description need to also be on the same sheet. Please have a look at the desired output below:

Output - Sheet 1:

Period	Line Description
May	xyz
May	xyz
May	yyy
May	yyy

Output - Sheet 2:

Period	Line Description
May	abc
May	abc
May	abc

line description 'abc' is on another sheet because it has 3 rows and cant be shared with any other line description because 'xyz' & 'yyy' each have 2 rows, therefore it will exceed the limit of rows per sheet which is 4 rows.

Output - Sheet 3:

Period	Line Description
June	xyz
June	xyz
June	abc
June	bbb

Output - Sheet 4:

Period	Line Description
June	xxx

In the case of June , it does not matter which line description gets distributed to a different sheet alone as long as its not "xyz" (except of the two xyz line items are together within the same sheet).

I know this is a bit tricky and I might have not explained it very well. Please let me know if you have any questions.

[pdave87]

@luaimatari7 hi, attached workflow will provide output into different sheets based on the grouping logic you require however, I was not able to limit the output (to 4 rows) for 1 specific sheet but this is the closest solution I could think at the moment. While other experts may provide exact solution, you can test run attached workflow and let me know your thoughts.

[pdave87]

@luaimatari7 ok, this is a step ahead (and covers 99% of requirement) of my previous solution.

Use same workflow and limit the number of records to 4 (as highlighted below in output data config), all records below 4 will split into new sheets (with group reference) and records > 4 will split into new workbook/file with group name appearing as a reference in sheet-name (based on output config selection). I think is as far as it goes based on my knowledge. 

Regards,

Pratik

[DawnDuong]

hi @luaimatari7 

This can be done via an iterative macro.

I attached here the solution. If you want the limit per tab to be dynamic, you can add user interface to enable that.
Dawn.

[luaimatari7]

Hi @dawnduong

thank you for this. It actually worked on the sample data, however when I connected the actual data to the macro and changed the limit of rows to 20k instead of 4, its not functioning correct anymore. I think I did something wrong. Would really appreciate if you can help me here. Please see below what I changed on the macro settings.

1) I changed the configuration of the multirow formula to >20000+1 instead of >4+1:

2) I changed the configuration of the filter to be testing > 20000 instead of testing >4

below you can see what the multirow formula is actually doing now for the testing field:

Really appreciate your help ! Thanks again

[joshikalyani]

This is helpful.  But i Need to split into sheets on which i'll work further. eg. in the flow output showing the splitted data in sheet but this output tool can not be input for further operations.

If can help will be really helpful:)