Combine Columns Dynamic Array Formulas

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,151
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,552
Office Version
  1. 365
Platform
  1. Windows
.. 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
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top