CODEWITHSUNDEEP
excelexcel-formula
Automation Monkey
Automation Monkey

Reputation: 365

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:

Disciplines

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?

Upvotes: 0

Views: 294

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

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.

Upvotes: 2

Related Questions