Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading