Lookup & Reference
Repeat a compacted list cyclically with INDEX, FILTER and MOD
Skip gaps and empty-string entries, then cycle through the remaining list with a validated positive step number.
Generate endless round-robin shift & lead assignments with zero manual copy-paste!
COUNTA measures occupied cells but does not compact their positions. If a gap occurs inside a list, indexing the original range using COUNTA can select the gap and omit the last item. Formula-generated empty strings also make COUNTA unsuitable as a blank-filter definition.
This version constructs a numeric mask with 1-COUNTBLANK for each cell. It removes empty content while preserving numeric zero, whitespace and duplicates. The four-row source has room for one more item; expanding beyond A5 requires changing the range and mask.
This is deterministic cycling, not a persistent lead-allocation service: changing the list can change existing assignments. Save assigned results separately if past allocations must stay fixed.
| Day / Lead # (C) | MOD(C - 1, 3) + 1 | Assigned Rotation Item |
|---|---|---|
| 1 | MOD(0, 3) + 1 = 1 | "Day" (Item 1) |
| 2 | MOD(1, 3) + 1 = 2 | "Swing" (Item 2) |
| 3 | MOD(2, 3) + 1 = 3 | "Night" (Item 3) |
| 4 (Cycle wraps!) | MOD(3, 3) + 1 = 1 | "Day" (Back to Item 1!) |
| 5 | MOD(4, 3) + 1 = 2 | "Swing" (Item 2) |
Round-robin cyclic lookup for 1-based step number C2
Subtracting 1 before MOD and adding 1 after MOD converts 0-based modular arithmetic into 1-based INDEX positions.
The mask checks each source cell separately and stacks the numeric results; SUM of this mask gives the retained length.
=IF(COUNT(C2)<>1,"Invalid inputs",IF(OR(C2<1,C2<>INT(C2)),"Invalid inputs",IF(SUM(VSTACK(1-COUNTBLANK($A$2),1-COUNTBLANK($A$3),1-COUNTBLANK($A$4),1-COUNTBLANK($A$5)))=0,"No items",INDEX(FILTER($A$2:$A$5,VSTACK(1-COUNTBLANK($A$2),1-COUNTBLANK($A$3),1-COUNTBLANK($A$4),1-COUNTBLANK($A$5))),MOD(C2-1,SUM(VSTACK(1-COUNTBLANK($A$2),1-COUNTBLANK($A$3),1-COUNTBLANK($A$4),1-COUNTBLANK($A$5))))+1))))Track which full cycle / rotation round a row belongs to
Divide the zero-based step by the number of retained entries. The same compacted-list count must be used for assignment and cycle number. Empty lists return No items; blank, fractional or nonpositive steps return Invalid inputs.
=IF(COUNT(C2)<>1,"Invalid inputs",IF(OR(C2<1,C2<>INT(C2)),"Invalid inputs",IF(SUM(VSTACK(1-COUNTBLANK($A$2),1-COUNTBLANK($A$3),1-COUNTBLANK($A$4),1-COUNTBLANK($A$5)))=0,"No items",QUOTIENT(C2-1,SUM(VSTACK(1-COUNTBLANK($A$2),1-COUNTBLANK($A$3),1-COUNTBLANK($A$4),1-COUNTBLANK($A$5))))+1)))Try the example
Use the original values or change them to test your understanding.