Advertisements

Is there a cell based function that can spill the entries from a table column whilst inserting a defined number of blank rows between each entry?

For example, In the following image I have the function `=FILTER(DisciplineTbl, {1,0,0,0})`

in cell P32:

In column S I have inserted what I am hoping to achieve, ideally through a formula in S32 that can spill the contents down the column with the specied empty rows between entries. Is something like this possible with a cell based formula or will it require a VBA solution?

### >Solution :

Try:

```
=LET(
ζ, A1:A4,
δ, 5,
ε, δ + 1,
ξ, SEQUENCE(ε * ROWS(ζ) - δ, , 0),
IF(MOD(ξ, ε), "", INDEX(ζ, 1 + QUOTIENT(ξ, ε)))
)
```

where `δ`

(5 here) is the number of blank rows to be inserted between each entry.

`A1:A4`

can of course be replaced with a dynamic spill range.