Forum Discussion

SteveK14's avatar
SteveK14
Occasional Reader
Jun 03, 2026

Series fill a formula down a column automatically skipping a set number of rows

I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it...
community
excel
Formulas and Functions
Macros and VBA
Office Scripts
m_tarler's avatar
m_tarler
Silver Contributor
Jun 04, 2026

is there a reason you can't use a single spill formula to output all the rows?  something like:

=REDUCE(TAKE(input, 5), SEQUENCE(1000), LAMBDA( p, q, VSTACK( p, TAKE(input+q, 5))))

since I don't know what formula you are really doing it is hard to really formulate so the above is just in concept.  The same would be true for any VBA or script, it is hard to suggest when I don't understand the data structure and formula & output needed.  But in terms of VBA I would go with a variation on your first:

...

' Fill every 6th cell

For r = startRow + 6 To lastRow Step 6

ws.Range(col & r).Formula = [here you need to apply the change to the formula]

Next r

...

SteveK14's avatar
SteveK14
Occasional Reader
Jun 04, 2026

My apologies, I posted before I added the formula and had to wait for it to be approved before I could reply and add it. Thank you very much for responding. I'm not familiar with some of those commands you list, but if they accomplish this, great! Basically, I need the formula to increment the variable $A2, like a flash fill, in this formula, but every 6 rows.

=TAKE(DROP('Active EE HRODS 2026-05-21'!$A:$W, MATCH('DO Count'!$A2,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),0),5)

I'm matching a building code on the 'DO Count' sheet with a list of employees assigned to that building on the 'Active EE HRODS' sheet and listing the top 5 rows for that building on a separate sheet.




  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 04, 2026

    here are a couple of options for you to try:

    the REDUCE-LAMBDA version:

    =IFERROR(REDUCE({"Header","row","of","data"},
                  MATCH('DO Count'!$A2:A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0),
                  LAMBDA(p,q,VSTACK(p,
                                            CHOOSEROWS('Active EE HRODS 2026-05-21'!$A:$W,q+SEQUENCE(5,,0))
                                             ,""))
   ),"")

    in this case that {"Header",....} part is a 'throw away' row that can be used to create the header row of the data.  You can Probably replace that with 'Active EE HRODS 2026-05-21'!$A1:$W1 or you can wrap the whole function with a DROP(... ,1)

    The MATCH I copied from you (but would personally update it to XMATCH) with 1 difference being I used the entire range to be searched $A2:$A999  (or what ever range you need) or use a . after the colon to auto trim the range like $A2:.$A999

    Inside the LAMBDA it is stacking the found row + 4 more with a blank row to each prior found rows.  NOTE I used CHOOSEROWS with a SEQUENCE of 0,1,2,3,4 instead of TAKE(DROP(...)) but either would work.

     

    another option avoiding the LAMBDA functions is

    =IFERROR(CHOOSEROWS( VSTACK( "", 'Active EE HRODS 2026-05-21'!$A:$W),
                                            TOCOL( EXPAND( MATCH('DO Count'!$A2:.$A999,'Active EE HRODS 2026-05-21'!$A$2:$A$50000, 0)+SEQUENCE(,5),,6,1))),"")

    in this case I add a row before all the data that is 'blank' (i.e. the VSTACK in line 1)

    then I use CHOOSEROWS to select all the 'MATCH' rows and add 1,2,3,4,5 (similar to above but because of the added row I start at 1 instead of 0) in this case creating a grid of the desired row values.  I use EXPAND to add another column that are all the value 1 (i.e. the blank row I added in line 1) then use TOCOL to make it a single list of values.

     

    I did test this on a test sheet but because my test sheet didn't use the same names and ranges as you I hope my translation of those values are correct.  At the very least it gives some creative ideas on how to solve your problem without VBA