Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- Windows
APPEND2H !! recursive !! appends 2 arrays horizontally
Excel Formula:
=LAMBDA(a,b,i,
LET(j,MAX(1,i),
ca,COLUMNS(a),cb,COLUMNS(b),s,SEQUENCE(,ca+1),
IF(j=cb+1,IFNA(a,""),APPEND2H(IF(s=ca+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 | |||
| 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 | X | Y | Z | ||||||
| 6 | b | 2 | A | B | C | ||||||
| 7 | c | 3 | |||||||||
| 8 | ex1:use of index(i) argument (>1),when 2nd array is already defined as a table or name | ||||||||||
| 9 | (acts like a replace) (if I > columns(b) leaves a as it is | ||||||||||
| 10 | 1 | Item A | 1 | Product A | red | 23 | sold | ||||
| 11 | 2 | Item B | 2 | Product B | green | 34 | stock | ||||
| 12 | 3 | Item C | 3 | Product C | white | 45 | sold | ||||
| 13 | i=3 | ||||||||||
| 14 | 1 | Item A | red | 23 | sold | ||||||
| 15 | 2 | Item B | green | 34 | stock | ||||||
| 16 | 3 | Item C | white | 45 | sold | ||||||
| 17 | |||||||||||
| 18 | ex2: to append a date or any other row of an array | 15-03-21 | |||||||||
| 19 | =APPEND2H(--"15-3-21",D10:H12,2) | ||||||||||
| 20 | 15-03-21 | Product A | red | 23 | sold | ||||||
| 21 | 15-03-21 | Product B | green | 34 | stock | ||||||
| 22 | 15-03-21 | Product C | white | 45 | sold | ||||||
| 23 | |||||||||||
| 24 | ex3: append dates sequence =APPEND2H(SEQUENCE(3,,"15-3-21"),D10:H12,2) | ||||||||||
| 25 | 15-03-21 | Product A | red | 23 | sold | ||||||
| 26 | 16-03-21 | Product B | green | 34 | stock | ||||||
| 27 | 17-03-21 | Product C | white | 45 | sold | ||||||
| 28 | |||||||||||
| 29 | ex4: create unusual sequences array =APPEND2H(SEQUENCE(3),SEQUENCE(,3,4),) | ||||||||||
| 30 | 1 | 4 | 5 | 6 | |||||||
| 31 | 2 | 4 | 5 | 6 | |||||||
| 32 | 3 | 4 | 5 | 6 | |||||||
| 33 | |||||||||||
APPEND2H | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A5:E7 | A5 | =APPEND2H(A1:B3,D1:F2,) |
| A14:E16 | A14 | =APPEND2H(A10:B12,D10:H12,3) |
| A20:E22 | A20 | =APPEND2H(--"15-3-21",D10:H12,2) |
| A25:E27 | A25 | =APPEND2H(SEQUENCE(3,,"15-3-21"),D10:H12,2) |
| A30:D32 | A30 | =APPEND2H(SEQUENCE(3),SEQUENCE(,3,4),) |
| Dynamic array formulas. | ||
Upvote
0
