Creating a Dynamic Array from sparse but regular data-CodePudding

My data is regular but sparse. As an example, imagine 3 data points in A4, A14 and A24 ("a","b" and "c" respectively). I want to pull these into a table. I need it to be able to adapt to more data points (so there might be 20, might be 2, but always 10 rows apart starting in A4).

Currently, I'm handling this by using an index column (say B1:B3 with the array formula SEQUENCE(COUNTA(A:A),1,1,1), and then next to that having:

INDIRECT("R"&4 (B1-1)*10&"C1",FALSE)

but I have to drag this formula down, and I want it to update dynamically, the way the index column would.

I wanted to make this into a dynamic array by adapting the formula to:

INDIRECT("R"&4 (B1:B3-1)*10&"C1",FALSE)

INDIRECT("R"&4 (SEQUENCE(COUNTA(A:A),1,0,1)*10&"C1",FALSE)

It spills correctly, but both of these return arrays of #VALUE! errors instead of the expected {a,b,c}. Why would this be? Have you got any suggestions for handling this in other ways?

CodePudding user response：

Try this

=INDEX(A:A,SEQUENCE(COUNTA(A:A),1,4,10))

CodePudding user response：

for previous versions of Excel, without Sequence(), this will do

=INDEX(B1:B1000,(ROW(OFFSET($A$1,,,COUNT(B1:B1000)))-1)*10 4)

OR put the Distance (ex:10) in D3 / Start in D2 (ex:4) / Total no of Numbers in D1 (ex: D1 = Count(b1:b1000)) and write the formula:

=INDEX(B1:B1000,(ROW(OFFSET($A$1,,,$D$1))-1)*$D$3 $D$2)

up to you. (enter both as Array Formulas w ctrl shift enter)

B1:B1000 will contain up to 100 numbers... so adjust at will and keep small for speed