Math
Assign cohort / batch numbers every N rows with QUOTIENT, MOD, and ROW
Generate repeating group IDs (1,1,1, 2,2,2...) and within-batch seat positions (1,2,3, 1,2,3...) for any group size N using zero-based row offsets.
Assign groups by physical row position
For a contiguous list of seven records, subtract the first data row from the current row to obtain offsets 0 through 6. QUOTIENT(offset,N)+1 gives a group number; MOD(offset,N)+1 gives the position within that group. N must be a positive integer.
These are positional groups, not stable record IDs. Sorting changes group membership; hidden, filtered and blank physical rows still occupy positions. To group only qualifying records, first create a contiguous list of those records.
| Order Row (A) | Zero Index k | Pallet Group (N = 3) (B) | Box Slot 1..3 (C) |
|---|---|---|---|
| ORD-101 (Row 2) | k = 0 | QUOTIENT(0, 3) + 1 = 1 | MOD(0, 3) + 1 = 1 |
| ORD-102 (Row 3) | k = 1 | QUOTIENT(1, 3) + 1 = 1 | MOD(1, 3) + 1 = 2 |
| ORD-103 (Row 4) | k = 2 | QUOTIENT(2, 3) + 1 = 1 | MOD(2, 3) + 1 = 3 |
| ORD-104 (Row 5) | k = 3 | QUOTIENT(3, 3) + 1 = 2 | MOD(3, 3) + 1 = 1 (New Pallet!) |
| ORD-107 (Row 8) | k = 6 | QUOTIENT(6, 3) + 1 = 3 | MOD(6, 3) + 1 = 1 |
Batch / Pallet group ID incrementing every E2 rows
The $A$2 anchor stays fixed when copying the formula down. Inserting or deleting rows can change references and group membership; review the resulting range after structural edits.
=IF(COUNT(E2)<>1,"Invalid group size",IF(OR(E2<1,E2<>INT(E2)),"Invalid group size",QUOTIENT(ROW(A5) - ROW($A$2), $E$2) + 1))Within-group slot number (1 to E2) & total pallets needed
For the contiguous nonempty list here, ROUNDUP(COUNTA(A2:A8)/E2,0) counts required groups. COUNTA can count formulas returning empty strings; blank gaps also make this record count differ from the physical-row grouping.
=IF(COUNT(E2)<>1,"Invalid group size",IF(OR(E2<1,E2<>INT(E2)),"Invalid group size",MOD(ROW(A5) - ROW($A$2), $E$2) + 1))Try the example
Use the original values or change them to test your understanding.