google-sheetscountgoogle-sheets-formulatransposeflatten

formula(s) to add "page breaks" to Google Sheets or x number of rows dynamically

I have a sample GS here with multiple orders and each order has a different variety of rows.

I print out each order on its own page and I'm wondering if there's a way to do this via formulas.

One example might be, I have 3 orders, each with 5-10 items so each order fits neatly on a page. I'd like to print once and have 3 pages print, one for each order.

An advanced example might be, I have 5 orders. 3 orders have few items so each order fits neatly on a page. But the other orders might have enough items to be multi-page orders. Again, I'd like to hit print once and have at least 5 pages print out.

Is there a way to do something like this? I started querying to count the number of lines each order has and considered something with the Sequence function.

Solution

  • try:

    =INDEX({input!A1:D1; TRIM(SUBSTITUTE(QUERY(""&SPLIT(FLATTEN({
 FLATTEN(QUERY(TRANSPOSE(IF(input!A2:A="",,"×"&input!A2:D&"9E+99")),,9^9)), 
 TEXT(IF(input!A2:A="",,IFNA(VLOOKUP(input!A2:A&" "&
 COUNTIFS(input!A2:A, input!A2:A, ROW(input!A2:A), "<="&ROW(input!A2:A)), {input!A2:A&" "&
 COUNTIFS(input!A2:A, input!A2:A), IF(input!A2:A="",,
 REPT("9E+99 ", COLUMNS(input!A1:D1)))}, 2, 0))), SPLIT(REPT("@×", 
 4 + N("<< number of rows between")), 
 "×")), FLATTEN(QUERY(TRANSPOSE(IF(input!A2:A="",,IFNA(VLOOKUP(input!A2:A&" "&
 COUNTIFS(input!A2:A, input!A2:A, ROW(input!A2:A), "<="&ROW(input!A2:A)), {input!A2:A&" "&
 COUNTIFS(input!A2:A, input!A2:A), IF(input!A2:A="",,input!A1:D1&"9E+99")}, 
 SEQUENCE(1, COLUMNS(input!A:D))+1, 0)))),,9^9))}), "9E+99", 0), 
 "where Col3 is not null limit "&(COUNTUNIQUE(input!A2:A))*(
 4 + N("<< number of rows between"))+COUNTA(input!A2:A), 0), "×", ))})

    enter image description here

    for more or less rows in between change both 4s