Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- Windows
APPEND2V !! recursive !! appends 2 arrays vertically
Excel Formula:
=LAMBDA(a,b,i,
LET(j,MAX(1,i),
ra,ROWS(a),rb,ROWS(b),s,SEQUENCE(ra+1),
IF(j=rb+1,IFNA(a,""),APPEND2V(IF(s=ra+1,INDEX(IF(b="","",b),j,),IF(a="","",a)),b,j+1))
)
)| LAMBDA 5.0.xlsm | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | a | 1 | X | Y | Z | |||||||||
| 2 | b | 2 | A | B | C | |||||||||
| 3 | c | 3 | ||||||||||||
| 4 | for appending entire arrays,i can be ignored 0 or 1 | |||||||||||||
| 5 | a | 1 | ||||||||||||
| 6 | b | 2 | ||||||||||||
| 7 | c | 3 | ||||||||||||
| 8 | X | Y | Z | |||||||||||
| 9 | A | B | C | |||||||||||
| 10 | ||||||||||||||
| 11 | ex1:use of index(i) argument (>1),when 2nd array is already defined as a table or name | |||||||||||||
| 12 | (acts like a replace) (if I > rows(b) leaves a as it is | |||||||||||||
| 13 | 1 | Product D | blue | 17 | stock | 1 | Product A | red | 23 | sold | ||||
| 14 | 2 | Product E | magenta | 12 | sold | 2 | Product B | green | 34 | stock | ||||
| 15 | i=3 | 3 | Product C | white | 45 | sold | ||||||||
| 16 | 1 | Product D | blue | 17 | stock | |||||||||
| 17 | 2 | Product E | magenta | 12 | sold | |||||||||
| 18 | 3 | Product C | white | 45 | sold | |||||||||
| 19 | ex2: appends heders to an array | |||||||||||||
| 20 | =APPEND2V(CHAR(SEQUENCE(,5,65)),A16#,) | |||||||||||||
| 21 | A | B | C | D | E | |||||||||
| 22 | 1 | Product D | blue | 17 | stock | |||||||||
| 23 | 2 | Product E | magenta | 12 | sold | |||||||||
| 24 | 3 | Product C | white | 45 | sold | |||||||||
| 25 | =APPEND2V(SEQUENCE(,5)&" "&CHAR(SEQUENCE(,5,65)),A16#,1) | |||||||||||||
| 26 | 1 A | 2 B | 3 C | 4 D | 5 E | |||||||||
| 27 | 1 | Product D | blue | 17 | stock | |||||||||
| 28 | 2 | Product E | magenta | 12 | sold | |||||||||
| 29 | 3 | Product C | white | 45 | sold | |||||||||
| 30 | ||||||||||||||
| 31 | ex4: create unusual sequences array =APPEND2V(SEQUENCE(,3)^0,SEQUENCE(3,3,4),) | |||||||||||||
| 32 | 1 | 1 | 1 | |||||||||||
| 33 | 4 | 5 | 6 | |||||||||||
| 34 | 7 | 8 | 9 | |||||||||||
| 35 | 10 | 11 | 12 | |||||||||||
| 36 | ||||||||||||||
AAPPEND2V | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A5:C9 | A5 | =APPEND2V(A1:B3,D1:F2,) |
| A16:E18 | A16 | =APPEND2V(A13:E14,G13:K15,3) |
| A21:E24 | A21 | =APPEND2V(CHAR(SEQUENCE(,5,65)),A16#,) |
| A26:E29 | A26 | =APPEND2V(SEQUENCE(,5)&" "&CHAR(SEQUENCE(,5,65)),A16#,1) |
| A32:C35 | A32 | =APPEND2V(SEQUENCE(,3)^0,SEQUENCE(3,3,4),) |
| Dynamic array formulas. | ||
Upvote
0
