Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 832
- Office Version
- 365
- Platform
- Windows
ACLMJOIN array columns join, textjoins chosen pattern of columns indexes of an array, by a delimiter "d", and inserts the result after the "ni" new column index of remaining array . Calls ASELECT , ATEXTJOIN , ABINSERT
a: array; cm, column min ; cx, column max ; ec, extra columns ; ni, new column index ; d, delimiter
Example: imagine 10clms array. column pattern: cm=4,cx=7, ec={2,9} , ni=2 .
The formula extracts all columns btwn cm(min) and cx(max) and adds the extra columns, everything in ascending order from left to right. The extracted array will contain this sequence of columns {2,4,5,6,7,9}. The remaining array after extraction, will contain columns {1,3,8,10} . The formula ATEXTJOINs the extracted array in a single column and inserts it after 2nd new column index of remaining array (ni=2) so, between 3 and 8. The result array will look like this {1,3, extr.array joined , 8,10} or {1,3,(2,4,5,7,9),8,10}
The algorithm for column pattern is the same like in ASELECT, so if by mistake you introduce cm>cx, formula knows to put them in order, or values out of columns range will be ignored.
ni argument has the same behavior like ci argument of ABINSERT : -1, extracted textjoined array will be appended to the left of remaining array, 0 or ignored, to the right , or ni should be in this interval 1<=ni<columns(remaining array) . Out of this values, returns error mes. "check data", the message caried by ABINSERT functionality.
a: array; cm, column min ; cx, column max ; ec, extra columns ; ni, new column index ; d, delimiter
Example: imagine 10clms array. column pattern: cm=4,cx=7, ec={2,9} , ni=2 .
The formula extracts all columns btwn cm(min) and cx(max) and adds the extra columns, everything in ascending order from left to right. The extracted array will contain this sequence of columns {2,4,5,6,7,9}. The remaining array after extraction, will contain columns {1,3,8,10} . The formula ATEXTJOINs the extracted array in a single column and inserts it after 2nd new column index of remaining array (ni=2) so, between 3 and 8. The result array will look like this {1,3, extr.array joined , 8,10} or {1,3,(2,4,5,7,9),8,10}
The algorithm for column pattern is the same like in ASELECT, so if by mistake you introduce cm>cx, formula knows to put them in order, or values out of columns range will be ignored.
ni argument has the same behavior like ci argument of ABINSERT : -1, extracted textjoined array will be appended to the left of remaining array, 0 or ignored, to the right , or ni should be in this interval 1<=ni<columns(remaining array) . Out of this values, returns error mes. "check data", the message caried by ABINSERT functionality.
Excel Formula:
=LAMBDA(a,cm,cx,ec,ni,d,
LET(c,COLUMNS(a),s,SEQUENCE(,c),as,ASELECT(a,,,cm,cx,ec),y,ATEXTJOIN(as,,,d),ss,ASELECT(s,,,cm,cx,ec),
x,FILTER(a,ISNA(XMATCH(s,ss))),
ABINSERT(x,y,ni)
)
)| LAMBDA 8.0.xlsx | ||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | |||
| 1 | sample array | Join all numeric columns to the right of letters array | Join all letter columns to the left of numbers array | Join all letters columns after second column of numeric array | ||||||||||||||||||||||||||||||||||
| 2 | =ACLMJOIN(A3:J7,6,8,{2,10},,",") | =ACLMJOIN(A3:J7,3,5,{1,9},-1,"/") | =ACLMJOIN(A3:J7,3,5,{1,9},2,"/") | |||||||||||||||||||||||||||||||||||
| 3 | A | 1 | F | K | P | 6 | 11 | 16 | AB | 21 | A | F | K | P | AB | 1,6,11,16,21 | A/F/K/P/AB | 1 | 6 | 11 | 16 | 21 | 1 | 6 | A/F/K/P/AB | 11 | 16 | 21 | ||||||||||
| 4 | B | 2 | G | L | Q | 7 | 12 | 17 | CD | 22 | B | G | L | Q | CD | 2,7,12,17,22 | B/G/L/Q/CD | 2 | 7 | 12 | 17 | 22 | 2 | 7 | B/G/L/Q/CD | 12 | 17 | 22 | ||||||||||
| 5 | C | 3 | H | M | R | 8 | 13 | 18 | EF | 23 | C | H | M | R | EF | 3,8,13,18,23 | C/H/M/R/EF | 3 | 8 | 13 | 18 | 23 | 3 | 8 | C/H/M/R/EF | 13 | 18 | 23 | ||||||||||
| 6 | D | 4 | I | N | S | 9 | 14 | 19 | GH | 24 | D | I | N | S | GH | 4,9,14,19,24 | D/I/N/S/GH | 4 | 9 | 14 | 19 | 24 | 4 | 9 | D/I/N/S/GH | 14 | 19 | 24 | ||||||||||
| 7 | E | 5 | J | O | T | 10 | 15 | 20 | IJ | 25 | E | J | O | T | IJ | 5,10,15,20,25 | E/J/O/T/IJ | 5 | 10 | 15 | 20 | 25 | 5 | 10 | E/J/O/T/IJ | 15 | 20 | 25 | ||||||||||
| 8 | ||||||||||||||||||||||||||||||||||||||
| 9 | ||||||||||||||||||||||||||||||||||||||
| 10 | if wrong values introduced | |||||||||||||||||||||||||||||||||||||
| 11 | =ACLMJOIN(A3:J7,5,3,{1,11},2,"/") | |||||||||||||||||||||||||||||||||||||
| 12 | 1 | 6 | A/F/K/P | 11 | 16 | AB | 21 | |||||||||||||||||||||||||||||||
| 13 | 2 | 7 | B/G/L/Q | 12 | 17 | CD | 22 | |||||||||||||||||||||||||||||||
| 14 | 3 | 8 | C/H/M/R | 13 | 18 | EF | 23 | |||||||||||||||||||||||||||||||
| 15 | 4 | 9 | D/I/N/S | 14 | 19 | GH | 24 | |||||||||||||||||||||||||||||||
| 16 | 5 | 10 | E/J/O/T | 15 | 20 | IJ | 25 | |||||||||||||||||||||||||||||||
| 17 | wrong arguments handling | |||||||||||||||||||||||||||||||||||||
| 18 | if cm(min)>cx(max) (5>3) , no problem formula put them in order | |||||||||||||||||||||||||||||||||||||
| 19 | ec={1,11} value 11 in extra column argument is ignored (array has only 10 columns), formula considers only ec={1} | |||||||||||||||||||||||||||||||||||||
| 20 | ||||||||||||||||||||||||||||||||||||||
ACLMJOIN post | ||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| L2,Z11,Z2,S2 | L2 | =FORMULATEXT(L3) |
| L3:Q7 | L3 | =ACLMJOIN(A3:J7,6,8,{2,10},,",") |
| S3:X7 | S3 | =ACLMJOIN(A3:J7,3,5,{1,9},-1,"/") |
| Z3:AE7 | Z3 | =ACLMJOIN(A3:J7,3,5,{1,9},2,"/") |
| Z12:AF16 | Z12 | =ACLMJOIN(A3:J7,5,3,{1,11},2,"/") |
| Dynamic array formulas. | ||
Upvote
0
