Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
ABINSERT inserts array "b" to the right of column index "ci" of array "a" . Calls APPEND2H
ci: column index argument, should be in interval 1<=ci<columns(a), special values: -1,appends array "b" to the left of "a", 0 or ignored, appends array "b" to the right of "a" .
Note: any other values out of the interval or other than special values -1,0 , returns an error mes. "check data". If ci=columns(a) still error mes. (insert after last column is not inserting, is appending, so we have to use ci=0 for that scenario)
The (ci+1)'s column of result array will be the 1st column of "b" array
ci: column index argument, should be in interval 1<=ci<columns(a), special values: -1,appends array "b" to the left of "a", 0 or ignored, appends array "b" to the right of "a" .
Note: any other values out of the interval or other than special values -1,0 , returns an error mes. "check data". If ci=columns(a) still error mes. (insert after last column is not inserting, is appending, so we have to use ci=0 for that scenario)
The (ci+1)'s column of result array will be the 1st column of "b" array
Excel Formula:
=LAMBDA(a,b,ci,
SWITCH(ci,0,APPEND2H(a,b,),-1,APPEND2H(b,a,),LET(c,COLUMNS(a),s,SEQUENCE(,c),x,FILTER(a,s<=ci),y,FILTER(a,s>ci),IFERROR(APPEND2H(APPEND2H(x,b,),y,),"check data"))
)
)| LAMBDA 8.0.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
| 1 | array A | array B | |||||||||||||||||
| 2 | =SEQUENCE(5,5) | =ABINSERT(A3#,G3:H5,3) | |||||||||||||||||
| 3 | 1 | 2 | 3 | 4 | 5 | a | x | 1 | 2 | 3 | a | x | 4 | 5 | |||||
| 4 | 6 | 7 | 8 | 9 | 10 | b | y | 6 | 7 | 8 | b | y | 9 | 10 | |||||
| 5 | 11 | 12 | 13 | 14 | 15 | c | z | 11 | 12 | 13 | c | z | 14 | 15 | |||||
| 6 | 16 | 17 | 18 | 19 | 20 | 16 | 17 | 18 | 19 | 20 | |||||||||
| 7 | 21 | 22 | 23 | 24 | 25 | 21 | 22 | 23 | 24 | 25 | |||||||||
| 8 | |||||||||||||||||||
| 9 | ci column index | =ABINSERT(A3#,G3:H5,) | |||||||||||||||||
| 10 | -1 appends array b to the left of array a | 1 | 2 | 3 | 4 | 5 | a | x | |||||||||||
| 11 | 0 or ignored,appends b to the right of a | 6 | 7 | 8 | 9 | 10 | b | y | |||||||||||
| 12 | 1<=ci<columns(a) ( column [ci+1] of result array | 11 | 12 | 13 | 14 | 15 | c | z | |||||||||||
| 13 | will be first column of b array ) | 16 | 17 | 18 | 19 | 20 | |||||||||||||
| 14 | (values out of above interval triggers | 21 | 22 | 23 | 24 | 25 | |||||||||||||
| 15 | error message "check data") | ||||||||||||||||||
| 16 | =ABINSERT(A3#,G3:H5,-1) | ||||||||||||||||||
| 17 | =ABINSERT(A3#,G3:H5,5) | a | x | 1 | 2 | 3 | 4 | 5 | |||||||||||
| 18 | check data | b | y | 6 | 7 | 8 | 9 | 10 | |||||||||||
| 19 | (ci should be < columns(a) | c | z | 11 | 12 | 13 | 14 | 15 | |||||||||||
| 20 | if we want b to the right of a ci=0,or ignored) | 16 | 17 | 18 | 19 | 20 | |||||||||||||
| 21 | 21 | 22 | 23 | 24 | 25 | ||||||||||||||
| 22 | =ABINSERT(A3#,G3:H5,10) | ||||||||||||||||||
| 23 | check data | =ABINSERT(A3#,G3:H5,1) | |||||||||||||||||
| 24 | 1 | a | x | 2 | 3 | 4 | 5 | ||||||||||||
| 25 | =ABINSERT(A3#,G3:H5,-2) | 6 | b | y | 7 | 8 | 9 | 10 | |||||||||||
| 26 | check data | 11 | c | z | 12 | 13 | 14 | 15 | |||||||||||
| 27 | 16 | 17 | 18 | 19 | 20 | ||||||||||||||
| 28 | 21 | 22 | 23 | 24 | 25 | ||||||||||||||
| 29 | |||||||||||||||||||
ABINSERT post | |||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2,A25,J23,A22,A17,J16,J9,J2 | A2 | =FORMULATEXT(A3) |
| A3:E7 | A3 | =SEQUENCE(5,5) |
| J3:P7 | J3 | =ABINSERT(A3#,G3:H5,3) |
| J10:P14 | J10 | =ABINSERT(A3#,G3:H5,) |
| J17:P21 | J17 | =ABINSERT(A3#,G3:H5,-1) |
| A18 | A18 | =ABINSERT(A3#,G3:H5,5) |
| A23 | A23 | =ABINSERT(A3#,G3:H5,10) |
| J24:P28 | J24 | =ABINSERT(A3#,G3:H5,1) |
| A26 | A26 | =ABINSERT(A3#,G3:H5,-2) |
| Dynamic array formulas. | ||
Upvote
0
