CODEWITHSUNDEEP
arraysexcelexcel-formula
LEG
LEG

Reputation: 3

Is there a way to duplicate every value in an excel array?

I am trying to duplicate all values in an array in my sheet. I have {1,6,14,15} and I want to output {1,1,6,6,14,14,15,15}. I would like to do this exclusively with functions. I have seen the VSTACK function, which seems very useful, however joining the insider thing seems like a hassle and would not allow this spreadsheet to be usable across other devices easily.

I have tried the CONCAT function, however this simply returns 161415161415 which is not helpful to me. The various alternatives to VSTACK all remove duplicates, which is exactly not what I am looking for. Besides all of those alternatives are lengthy and hard for me to wrap my head around.

Upvotes: 0

Views: 2246

Answers (3)

cbapel
cbapel

Reputation: 1

Thanks for introducing me to expand! Here's the formula I'm now using. The logic is to first create an array with myCount x myRepeat dimensions populated with 1's. Then, I multiply this array with the array I want repeated using sequence, and then the tocol formula to adjust the range. This works because of how Excel multiplies arrays.

=LET(myStart,0,myCount,3,myRepeat,3,myStep,3,EXPAND(SEQUENCE(myCount,,1,0),,myRepeat,1)*SEQUENCE(myCount,,myStart,myStep))

{1,1,1;1,1,1;1,1,1} * {0,3,6} = {0,0,0;3,3,3;6,6,6}

=LET(myStart,0,myCount,3,myRepeat,3,myStep,3,TOCOL(EXPAND(SEQUENCE(myCount,,1,0),,myRepeat,1)*SEQUENCE(myCount,,myStart,myStep)))

Result in the sequence of number starting at 0 (myStart), stepping 3 (myStep), for a count of 3 (myCount), and each step is repeated 3 times (myRepeat).

{0,0,0,3,3,3,6,6,6}

I regularly need this type of array to handle offsets, thanks to this thread I think I've finally found a formula I'm happy with. Thanks!

Upvotes: 0

JvdV
JvdV

Reputation: 75920

You could use EXPAND() here:

=LET(arr,{1,6,14,15},TOROW(IFERROR(EXPAND(arr,2),arr),,1))

Note that 2 will define how often you want to duplicate the input.

Upvotes: 5

Jos Woolley
Jos Woolley

Reputation: 9062

If you have LET and SEQUENCE:

=LET(ζ,{1,6,14,15},INDEX(ζ,SEQUENCE(,2*COUNTA(ζ),,0.5)))

Upvotes: 1

Related Questions