# Combine Columns Dynamic Array Formulas

#### lrobbo314

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

Excel Formula:
``=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
ABCDEFGHI
1510612712510
24871381348
335484835
451241539512
541528517415
6612
7713
848
9415
1028
11712
12813
1348
1439
15517
Sheet3

#### Xlence

##### New Member
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.

Excel Formula:
``````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.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Peter_SSs

##### MrExcel MVP, Moderator
.. 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

Excel Formula:
``````=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

##### New Member
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.

Replies
6
Views
209
Replies
2
Views
435
Replies
14
Views
445
Replies
11
Views
208
Replies
26
Views
697

1,130,042
Messages
5,639,722
Members
417,107
Latest member
derekMG

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back