Dynamic Arrays
Filter every Nth row: extract periodic intervals and audit samples
Extract every 2nd, 3rd, or Nth row from a production log into a live summary list using a relative row-index MOD flag and FILTER.
Anchor relative row index to 1..N before applying MOD
Using raw MOD(ROW(), N) = 0 depends on where your table starts on the worksheet—inserting a banner row above the table shifts every sampled record!
Computing relative 1-based position relRow = ROW() - ROW($A$2) + 1 guarantees the first data row is ALWAYS 1: MOD(relRow, N) = 0 selects rows N, 2N, 3N (every Nth end-of-block), while MOD(relRow - 1, N) = 0 selects rows 1, 1+N, 1+2N.
| BatchID (A) | RelRow | MOD(RelRow, 3)=0 (B) | Sampled When N = 3 |
|---|---|---|---|
| LOT-101 | 1 | FALSE | — |
| LOT-102 | 2 | FALSE | — |
| LOT-103 | 3 | TRUE | Sample #1 |
| LOT-104 | 4 | FALSE | — |
| LOT-105 | 5 | FALSE | — |
| LOT-106 | 6 | TRUE | Sample #2 |
Dynamic Nth-row extraction formula
Point MOD's divisor at cell $D$2 so auditors can switch from every 3rd lot to every 2nd lot instantly.
=TEXTJOIN(", ", TRUE, FILTER(A2:A7, B2:B7, "None"))Row-level periodic sampling flag
Evaluates TRUE on rows N, 2N, 3N, ... relative to $A$2.
=MOD(ROW(A4) - ROW($A$2) + 1, $D$2) = 0Inputs and interpretation
This is a deterministic selection with a fixed starting offset, not a random or necessarily representative audit sample. Periodicity in the source may bias the result. Use a positive integer interval; sorting or inserting rows changes which records are selected. TEXTJOIN returns the selected values in one cell.
Try the example
Use the original values or change them to test your understanding.