Combine Columns Dynamic Array Formulas

[lrobbo314]

In a previous thread @Peter_SSs used a technique using Index with arrays to combine multiple columns to 1 single column array.

=IF(F3=F2,"",LET(a,FILTER(B$3:E$22,F$3:F$22=F3),r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),TEXTJOIN(", ",1,UNIQUE(FILTER(arr,arr<>0)))))

I wanted to know if it was possible to do something similar, but instead combine the 6 column table below to a 2 column table.

Book1
	A	B	C	D	E	F	G	H	I
1	5	10	6	12	7	12		5	10
2	4	8	7	13	8	13		4	8
3	3	5	4	8	4	8		3	5
4	5	12	4	15	3	9		5	12
5	4	15	2	8	5	17		4	15
6								6	12
7								7	13
8								4	8
9								4	15
10								2	8
11								7	12
12								8	13
13								4	8
14								3	9
15								5	17
Sheet3

 

[Xlence]

Really great work on an important challenge @Peter_SSs, @jasonb75, @StephenCrump.

I generalized the solution even further to be able to append multiple non-contiguous table columns with varying number of rows. This method is designed to work even when the data is in different tables (table objects) or sheets. The only small caveat is that at least one of the appended columns must not contain an #N/A error.

The example below assumes we want to append 2 columns from 2 tables, where we want to append only Columns A and B, ignoring any other columns. Note that we assume Column A does not contain any #N/A's in neither of the two tables, otherwise those rows would be ignored.

LET(
  arr, CHOOSE({1, 2, 3, 4}, Table1[ColumnA], Table1[ColumnB], Table2[ColumnA], Table2[ColumnB]),
  r, ROWS(arr),
  c, 2,
  seq, SEQUENCE(r*COLUMNS(arr)/c,,0),
  arr2, INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)),
  FILTER(arr2, NOT(ISNA(INDEX(arr2, , 1))))
)

* I wasn't able to test this fully because my Excel version doesn't yet have the LET function, but hopefully it works as described.

 

[Peter_SSs]

.. hopefully it works as described.

Not quite - you cannot use arr2 as a name in a LET function since it is also a valid cell reference. So you just have to change that name. For example

=LET(
  arr, CHOOSE({1,2,3,4}, Table1[ColumnA], Table1[ColumnB], Table2[ColumnA], Table2[ColumnB]),
  r, ROWS(arr),
  c, 2,
  seq, SEQUENCE(r*COLUMNS(arr)/c,,0),
  arr_2, INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)),
  FILTER(arr_2, NOT(ISNA(INDEX(arr_2, , 1))))
)

 

[Xlence]

Not quite - you cannot use arr2 as a name in a LET function since it is also a valid cell reference. So you just have to change that name.

Good catch! Hopefully LET will be rolled out soon.