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

Question

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:

PeriodLine DescriptionMayxyzMayxyzMayabcMayabcMayabcMayyyyMayyyyJunexyzJunexyzJuneabcJunebbbJunexxx

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:

PeriodLine DescriptionMayxyzMayxyzMayyyyMayyyy

Output - Sheet 2:

PeriodLine DescriptionMayabcMayabcMayabc

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:

PeriodLine DescriptionJunexyzJunexyzJuneabcJunebbb

Output - Sheet 4:

PeriodLine DescriptionJunexxx

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.

joshikalyani · Answer

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

luaimatari7 · Answer

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

DawnDuong · Answer

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.

Step1_CountPerGroup.yxzp