Forum Discussion
Copy Array N times with excel formula
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;
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:
And while the solutions involved column arrays, I'm having trouble applying those solutions to row arrays.
Any help would be greatly appreciated! Thanks in advance!
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 / SEQUENCE
Please see the attached workbook, if desired...
2 Replies
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 / SEQUENCE
Please see the attached workbook, if desired...
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.
