Spill table column with specified number of blank rows between entries in excel

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 :


    ζ, 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.

Leave a Reply