SOLVED

Copy Array N times with excel formula

Copper Contributor

Hello everyone,

 

I am working on defining a rectangle using two points: the initial point and the end point. What I want to achieve is to subdivide the segments of the rectangle N times based on both the X and Y coordinates, and then get the coordinates of these points. The goal is to get these coordinates in two rows (I thought maybe an array should work in this case). I have managed to get arrays using the SEQUENCE command, but I'm having difficulty duplicating that array multiple times.

 

Coordinate X =SEQUENCE(1;G4+1;C5;I4)

Coordinate Y =SEQUENCE(1;H4+1;D5;J4)

 

What I need now is to repeat these array N times.

 

This is an example for these inputs;

 

Borja710_0-1705471774811.png

 

Coordinate X: {0,5,10,0,5,10,0,5,10,0,5,10,0,5,10,0,5,10}

 

Coordinate Y: {0,0,0,2,2,2,4,4,4,6,6,6,8,8,8,10,10,10}

 

As you can see, what I want is:

 

For X coordinates, multiply the initial array {0,5,10} 5 (Y Subdivision)+1 times to get {0,5,10,0,5,10,0,5,10,0,5,10,0,5,10,0,5,10}

 

For Y coordinates, repeat each number of the array {0,2,4,6,8,10} 2 (X Subdivision)+1 times getting {0,0,0,2,2,2,4,4,4,6,6,6,8,8,8,10,10,10}

 

I've read some posts on this issue such as this one:

 

https://techcommunity.microsoft.com/t5/excel/creating-a-dynamic-repeating-sequence-of-arrays/m-p/340... 

 

And while the solutions involved column arrays, I'm having trouble applying those solutions to row arrays. 

 

Borja710_0-1705442135908.png

Any help would be greatly appreciated! Thanks in advance!

2 Replies

@Borja710 

This is the formula in cell A7:

=TOROW(IFNA(EXPAND(SEQUENCE(1;G4+1;C5;I4);H4+1);SEQUENCE(1;G4+1;C5;I4)))

 

This is the formula in cell A8:

=TOROW(IFNA(EXPAND(SEQUENCE(1;H4+1;D5;J4);G4+1);SEQUENCE(1;H4+1;D5;J4));;TRUE)

 

With Office 365 or Excel for the web you can apply these formulas.

copy array n times.png

best response confirmed by Borja710 (Copper Contributor)
Solution

@Borja710 My favorite method for repeating values in Excel for MS365 is the IF / SEQUENCE method. For example, to repeat the word "Test" 3 times in a 1 column x 3 row array, use the following formula:

 

=IF(SEQUENCE(3), "Test")

 

Basically, the IF function treats any numeric value in the logical_test argument as TRUE (with the exception of 0, which is treated as FALSE). Since SEQUENCE(3) returns {1;2;3}, the IF function reads this as {TRUE;TRUE;TRUE}, and since the [value_if_true] is "Test", it returns {"Test";"Test";"Test"} as the final result.

 

This method can also be used to repeat a one-dimensional range or array. For example, to repeat a single row of data 3 times, the same formula can be applied as follows:

 

=IF(SEQUENCE(3), A1:C1)

 

Likewise, to repeat a single column of data 3 times, the same formula can be used, but with the [columns] parameter instead:

 

=IF(SEQUENCE(, 3), A1:A3)

 

This can also be combined with either TOROW or TOCOL to output the results to a single row or column. For example, your X-axis points can be generated and output to a single row with the following formula:

 

=TOROW(IF(SEQUENCE(6), SEQUENCE(, 3, 0, 5)))

 

Likewise, the formula for the Y-axis points would be virtually the same, but with the rows and [columns] arguments reversed:

 

=TOROW(IF(SEQUENCE(, 3), SEQUENCE(6,, 0, 2)))

 

And with the necessary variables input directly on the worksheet, the final formula would look something like this (combined with the VSTACK function):

 

=VSTACK(
   TOROW(IF(SEQUENCE(E5+1), SEQUENCE(, E4+1, C4, F4))),
   TOROW(IF(SEQUENCE(, E4+1), SEQUENCE(E5+1,, C5, F5)))
)

 

Repeating Arrays with IF / SEQUENCERepeating Arrays with IF / SEQUENCE

 

Please see the attached workbook, if desired...

1 best response

Accepted Solutions
best response confirmed by Borja710 (Copper Contributor)
Solution

@Borja710 My favorite method for repeating values in Excel for MS365 is the IF / SEQUENCE method. For example, to repeat the word "Test" 3 times in a 1 column x 3 row array, use the following formula:

 

=IF(SEQUENCE(3), "Test")

 

Basically, the IF function treats any numeric value in the logical_test argument as TRUE (with the exception of 0, which is treated as FALSE). Since SEQUENCE(3) returns {1;2;3}, the IF function reads this as {TRUE;TRUE;TRUE}, and since the [value_if_true] is "Test", it returns {"Test";"Test";"Test"} as the final result.

 

This method can also be used to repeat a one-dimensional range or array. For example, to repeat a single row of data 3 times, the same formula can be applied as follows:

 

=IF(SEQUENCE(3), A1:C1)

 

Likewise, to repeat a single column of data 3 times, the same formula can be used, but with the [columns] parameter instead:

 

=IF(SEQUENCE(, 3), A1:A3)

 

This can also be combined with either TOROW or TOCOL to output the results to a single row or column. For example, your X-axis points can be generated and output to a single row with the following formula:

 

=TOROW(IF(SEQUENCE(6), SEQUENCE(, 3, 0, 5)))

 

Likewise, the formula for the Y-axis points would be virtually the same, but with the rows and [columns] arguments reversed:

 

=TOROW(IF(SEQUENCE(, 3), SEQUENCE(6,, 0, 2)))

 

And with the necessary variables input directly on the worksheet, the final formula would look something like this (combined with the VSTACK function):

 

=VSTACK(
   TOROW(IF(SEQUENCE(E5+1), SEQUENCE(, E4+1, C4, F4))),
   TOROW(IF(SEQUENCE(, E4+1), SEQUENCE(E5+1,, C5, F5)))
)

 

Repeating Arrays with IF / SEQUENCERepeating Arrays with IF / SEQUENCE

 

Please see the attached workbook, if desired...

View solution in original post