# 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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### StephenCrump

##### MrExcel MVP
ABCDEFGHI
1MyDataNoCols (N)2
2510612712510
34871381348
435484835
551241539512
641528517415
7612
8713
948
10415
1128
12712
13813
1448
1539
16517
Sheet1
Cell Formulas
RangeFormula
H2:I16H2=INDEX(MyData,MOD(SEQUENCE(ROWS(MyData)*COLUMNS(MyData)/N,,0),ROWS(MyData))+SEQUENCE(,N,1,0),SEQUENCE(,N)+N*INT(SEQUENCE(ROWS(MyData)*COLUMNS(MyData)/N,,0)/ROWS(MyData)))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
MyData=Sheet1!\$A\$2:\$F\$6H2
N=Sheet1!\$I\$1H2

Or more succinctly
=LET(M,ROWS(MyData)*COLUMNS(MyData)/N,R, ROWS(MyData),INDEX(MyData,MOD(SEQUENCE(M,,0),R)+SEQUENCE(,N,1,0),SEQUENCE(,N)+N*INT(SEQUENCE(M,,0)/R)))

Last edited:

#### jasonb75

##### Well-known Member
Something like this?

Edit:- ignore this, just noticed that the results in the first column are wrong from rows 6 to 15
Book3
ABCDEFGHI
1510612712510
24871381348
335484835
451241539512
541528517415
61012
7813
858
91215
10158
11612
12713
1348
1449
15217
Sheet5
Cell Formulas
RangeFormula
H1:I15H1=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(COUNT(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r)+1)*SEQUENCE(,c)))
Dynamic array formulas.

#### jasonb75

##### Well-known Member
Corrected version, fixed it too late to edit previous reply.
Book3
ABCDEFGHI
1510612712510
24871381348
335484835
451241539512
541528517415
6612
7713
848
9415
1028
11712
12813
1348
1439
15517
Sheet5
Cell Formulas
RangeFormula
H1:I15H1=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(COUNT(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)))
Dynamic array formulas.

#### Peter_SSs

##### MrExcel MVP, Moderator

@jasonb75
You no doubt already know but that will not work if there are any text values, blanks or errors in the range.

This was my effort. (This & Stephens return zero for blanks in the range)

21 02 22.xlsm
ABCDEFGHI
1510612712510
24871381348
335484835
451241539512
541528517415
6612
7713
848
9415
1028
11712
12813
1348
1439
15517
6 cols to 2
Cell Formulas
RangeFormula
H1:I15H1=LET(rng,A1:F5,rws,ROWS(rng),num,rws*COLUMNS(rng)/2,seq,SEQUENCE(num,,0),INDEX(rng,MOD(seq,rws)+1,2*INT(seq/rws)+{1,2}))
Dynamic array formulas.

#### StephenCrump

##### MrExcel MVP
Thanks Peter

Borrowing @jasonb75's more succinct use of the LET, I'd modify the general case:

=LET(arr,A1:F5,r,ROWS(arr),c,2,seq,SEQUENCE(r*COLUMNS(arr)/c,,0),INDEX(arr,MOD(seq,r)+1,(INT(seq/r))*c+SEQUENCE(,c)))

#### jasonb75

##### Well-known Member

You no doubt already know but that will not work if there are any text values, blanks or errors in the range.
Now you mention it, Peter, it is something that I'm aware of.
I don't appear to be capable of thinking after midnight, at that time such possibilities often appear to elude me.

#### Peter_SSs

##### MrExcel MVP, Moderator
I don't appear to be capable of thinking after midnight, at that time such possibilities often appear to elude me.

#### lrobbo314

##### Well-known Member
Thanks everyone for the answers. Very cool. Stepping through the formula, I can see how you guys made it work, but how did you guys figure this out? I banged my head against the screen for a while trying to figure out the logic of it.

Just for info I am marking Stephen's answer as the answer just because I thought it was a nice touch that just changing the 1 variable let you control the number of columns it splits the data into. But again, thanks to all for the responses.

#### Peter_SSs

##### MrExcel MVP, Moderator
.. but how did you guys figure this out? I banged my head against the screen for a while ..
Most likely because I used a brick wall instead of a screen.

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,712
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