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

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.

CodePudding user response：

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.

CodePudding user response：

If you have LET and SEQUENCE:

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

CodePudding user response：

One way to duplicate every value in an Excel array is to use the TRANSPOSE function in combination with the ROW function. Here is an example of how you can use these functions to achieve your desired result:

=TRANSPOSE(ROW(1:4)^0)

This formula creates an array of four rows with the numbers 1, 2, 3, and 4 in each row. The ROW function returns the row number of each cell in the specified range (1:4), and the ^0 operator forces the row numbers to be treated as numbers rather than arrays. The TRANSPOSE function then transposes the array so that each number is in a separate column.

Here is an example of how the formula would be used in a worksheet:

    A       B       C       D
1   {1,1,1,1}   {2,2,2,2}   {3,3,3,3}   {4,4,4,4}
2   1         2          3        4

To duplicate the values in your original array {1,6,14,15}, you can use the INDEX function to return each value from the array and then use the formula above to duplicate the value in multiple columns. Here is an example of how you can do this:

=TRANSPOSE(ROW(1:4)^0)

=TRANSPOSE({1,6,14,15}*(ROW(1:4)^0))

In this example, the INDEX function is used to return the values {1,6,14,15} from the original array. These values are then multiplied by the array of row numbers to create an array of duplicate values:

    A       B       C       D       E       F
1   {1,1,1,1}   {6,6,6,6}   {14,14,14,14}   {15,15,15,15}
2   1         6          14         15

Note that this formula will only work if your original array is in a single