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
 

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
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
Something like this?

Edit:- ignore this, just noticed that the results in the first column are wrong from rows 6 to 15 :oops:
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
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,552
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,654
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,151
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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