Limit output row number to each tab in Excel with condition

[ThankForYourHelp]

Hi there,

I need support on the challenge design below:

I need to limit my data to 10,000 rows per tab in Excel. Here's an example:

If I have 5,000 rows for Tag 1 and 5,000 rows for Tag 2, I will have only one output tab in Excel, as the total number of rows equals 10,000.

However, if I have 3,000 rows for Tag 1 and 9,000 rows for Tag 2, I will need two output tabs in Excel. This is because the combined total exceeds the 10,000-row limit for a single tab.

I cannot split the data such that 3,000 rows of Tag 1 and 7,000 rows of Tag 2 are in one tab, leaving 2,000 rows of Tag 2 in a second tab. In this scenario, all 9,000 rows of Tag 2 should be placed in the second tab. It is important to ensure that data for each tag is kept together and not partially split across multiple tabs.

Thank you for your help.

Tag	data 1	data 2	data 3	data 4	data 5
1	data	data	data	data	data
2	data	data	data	data	data

More example:

more example:
	Tag	Rowcount	Tab Number in Excel
	1	3000	1
	2	9000	2
	3	2000	3
	4	4000	3

[PangHC · Accepted solution]

@ThankForYourHelp  you can counting the total row of Tag. then do some calculation on this to identify the sheet number.

then join back to the raw table with the sheet number.

both Multi-Row Tool Formula

Spoiler

1st (change the 100 to your record limit)

if ([Count]+[Row-1:Record Per Sheet]) > 100 then
  [Count]
else
  [Count]+[Row-1:Record Per Sheet]
endif

2nd

if [Record Per Sheet] = [Count] then
  [Row-1:SheetNumber]+1
else
  [Row-1:SheetNumber]
endif

[ThankForYourHelp]

Hi @PangHC  ,

for the case you showed, it should be all in sheet 1 in Excel.

any ideas on how to achieve that?

[ThankForYourHelp]

@PangHC  sorry, didn't pay attention to your code. you set to 100 instead 10,000. I'll give it a test and let you know. Thank you so much.

[ThankForYourHelp]

@PangHC  Could you attach your workflow? thank you

[PangHC]

the next step is do output to excel.
a. to different sheet - setting in output tool with "change filename\table name"
b. to different file - create a fullpath column and use that with "change entire filepath"

both method also have lot of resource in community.

and here the workflow.