Can someone help me fix this sheet(ctto.), It works well when the orders numbers are less than 100+/- but when the pending order numbers are too many it's giving me an error and it messes up the sheet.


(Column B, Sheet 2) =INDEX(IF(Sheet1!B2:B<>"",,Sheet1!A2:A)) linked to Sheet 1 for manual adding of (Date) to be auto remove to Sheet 2

(Column C, Sheet 2) =transpose(split(rept(join("|",D3:D)&"|",ceiling(count(A3:A)/COUNTA(D3:D))),"|",1,1))

  • which messes everything, Admin available vs. Pending Orders

(Column D, Sheet 2) manual edit depending on staff/admin available, will affect Column C data.

(Column E2, Sheet 2) =IFERROR(transpose(unique(C3:C))) to show admin names depends on Column D

(Column E-H, Sheet 2) =ifna(filter($B$3:$B,$C$3:$C=E2)) divide order numbers equally per staff

Feel free to edit/check this sheet as reference. Thank you!


1 Answer 1


Use vlookup() and mod(), like this:

      mod(row(B3:B), counta(D3:D)), 
      { mod(row(B3:B), counta(D3:D)), D3:D }, 

See your sample spreadsheet.

  • Amazing! Thank you so much!!!
    – Chris
    Sep 13, 2022 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.