CODEWITHSUNDEEP
excelexcel-formula
Matteo Castelli
Matteo Castelli

Reputation: 1

Any suggestions for automatically adding two empty rows beneath every value in a dynamic Excel list while using the FILTER function?

I'm currently working on a file that has a list of ISIN that is quite dynamic (it changes from month to month) and is used across many sheets. To keep this list up to date in all the sheets, I use the FILTER function, so that every time I update the information in my original list, it gets updated in all others sheets. However, in one the sheets in question, for formatting reasons, I would need for this list to have two empty rows below every single value.

I tried to manually write "=A2","=A3","A4", etc... every three rows and dragging the formula down, but to no avail, the list is very long, and doing it manually isn't an option. Does anyone have any suggestion on how to execute it? Thanks

Matteo

Example

Upvotes: 0

Views: 164

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

Perhaps you could try, without using LAMBDA()

enter image description here

• Formula used in cell C1

=LET(α,A1:A5,n,3,IF(MOD(SEQUENCE(ROWS(α)*n),n)=1,TOCOL(IFNA(α,SEQUENCE(,n))),""))

Note: If any time you want to make changes in the number of empty rows after every value, you can change that as well, dynamically by referencing the n to a cell, instead of hardcoding it within the formula.

Another alternative:

enter image description here

• Formula used in cell E1

=TEXTSPLIT(ARRAYTOTEXT(A1:A5&REPT(", ",2)),,", ")

Upvotes: 0

Ike
Ike

Reputation: 13014

You can use this formula:

=LET(d,A2:A5,
m,MAKEARRAY(ROWS(d),3,LAMBDA(r,c,IF(c=1,INDEX(d,r,1),""))),
TOCOL(m,,FALSE))

It creates a virtual array of three columns - where the second and third column are empty. Then this array is turned into a single column by row.

Upvotes: 0

Related Questions