How to combine dynamic arrays vertically

T

Tdw1990

New Member
Joined
Jul 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm trying to determine if it's possible to combine multiple arrays of dynamic data into one vertical array. I've tried using a SORTBY function in Cell A7 but it seems to try and combine the arrays horizontally. Is there a way to combine dynamic data vertically?

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBH
6SuiteTenantSq. Ft.Lease Expiration/StartJan 2022Feb 2022Mar 2022Apr 2022May 2022Jun 2022Jul 2022Aug 2022Sep 2022Oct 2022Nov 2022Dec 2022AnnualComments20Total Bldg Sqft124467125561125561125561125561125561124657124657124657124657124657124657
7#VALUE!1100JPMorgan Chase92534638792539253925392539253925392539253925392539253925317701Briggs & Caldwell, L.P.202044743000000202020202020202020202020
821005Qingfeng Tao, an Individual91145230911911911911911911911911911911911911181100VACANT1214644562121461214612146121461214612146121461214612146121461214612146
93101, 202, 220, 400, 805, GARAGE1World Cinema, Inc.42853042853428534285342853428534285342853428534285342853428534285319211VACANT109444593010941094109410941094109410941094109410941094
1041010S, 250Ersa Grae Corporation29240292429242924292429242924000000
115105Harvest Cafe116145565116111611161116111611161116111611161116111611161
1261060Offshore Contract Services, LLC325345107325332533253325332533253325332533253325332533253
137200Travis Davis146245199146214621462146214621462146214621462146214621462
148310United Concordia Companies, Inc.122245138122212221222122212221222122212221222122212221222
159320CompuTrain Business Solutions, Ltd.317645657317631763176317631763176317631763176317631763176
1610600EN Engineering, LLC1142747907114271142711427114271142711427114271142711427114271142711427
1711700KRBE Lico, Inc.1852145535185211852118521185211852118521185211852118521185211852118521
1812701Briggs & Caldwell, L.P.202044926202020202020202020202020202020202020202020202020
1913803Precision Task Group, Inc.634245504634263426342634263426342634263426342634263426342
2014920Leidos c/o Jones Lang LaSalle Americas779445443779477947794779477947794779477947794779477947794
2115SuiteTenant1Lease Expiration111111111111
2216SuiteTenant1Lease Expiration111111111111
Occupancy
Cell Formulas
RangeFormula
E6:P6E6='Y:\Ted Whiteford\[Fuller Budget Template.xlsx]Budget'!D9
AR6AR6=ROWS($AA$7#)+ROWS($AS$7#)+1
AW6:BH6AX6=SUM(INDEX($AW$7#,,COLUMN()-COLUMN($AV$6)),INDEX($AE$7#,,COLUMN()-COLUMN($AV$6)))
A7A7=SORTBY($AA$7#:$AS$7#,$Z$7#:$AR$7#,1)
Z7:Z22Z7=SEQUENCE(ROWS($AA$7#))
AA7:AD22AA7=FILTER('Y:\Ted Whiteford\[Fuller Budget Template.xlsx]In-Place Rent'!A94:D173,'Y:\Ted Whiteford\[Fuller Budget Template.xlsx]In-Place Rent'!Q94:Q173>0,"")
AE7:AP22AE7=FILTER('Y:\Ted Whiteford\[Fuller Budget Template.xlsx]In-Place Rent'!E94:P173,'Y:\Ted Whiteford\[Fuller Budget Template.xlsx]In-Place Rent'!Q94:Q173>0,"")
AR7:AR9AR7=SEQUENCE(ROWS($AS$7#),,ROWS($AA$7#)+1)
AS7:AV9AS7=FILTER('Y:\Ted Whiteford\[Fuller Budget Template.xlsx]Prospective Rent'!A128:D182,'Y:\Ted Whiteford\[Fuller Budget Template.xlsx]Prospective Rent'!W128:W182>0,"")
AW7:BH9AW7=FILTER('Y:\Ted Whiteford\[Fuller Budget Template.xlsx]Prospective Rent'!K128:V182,'Y:\Ted Whiteford\[Fuller Budget Template.xlsx]Prospective Rent'!W128:W182>0,"")
Dynamic array formulas.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
There's not a "good" way. They really should have included a UNION operator for dynamic arrays. But there are some workarounds. It's even harder when the arrays are disjoint. But take a look at this:

Book1
ABCDEFGHIJKLMN
1CombinedOneTwoThreeFourListValue
2TTCGAA86
3ZZERBB86
4CFVDC43
5EHXID83
6FKJE40
7HQLF26
8KUMG55
9QNH34
10UOI99
11GPJ89
12RSK43
13VWL100
14XYM82
15AN100
16BO91
17DP76
18IQ39
19JR68
20LS83
21MT25
22NU42
23OV62
24PW81
25SX51
26WY95
27YZ15
28
Sheet16
Cell Formulas
RangeFormula
B2:B27B2=IFERROR(INDEX(G2#,SEQUENCE(COUNTA((G2#,H2#,J2#,K2#)))),IFERROR(INDEX(H2#,SEQUENCE(COUNTA((G2#,H2#,J2#,K2#)))-COUNTA(G2#)),IFERROR(INDEX(J2#,SEQUENCE(COUNTA((G2#,H2#,J2#,K2#)))-COUNTA((G2#,H2#))),INDEX(K2#,SEQUENCE(COUNTA((G2#,H2#,J2#,K2#)))-COUNTA((G2#,H2#,J2#))))))
G2:G3G2=FILTER($M2:$M27,$N$2:$N$27<=25)
H2:H8H2=FILTER($M2:$M27,($N$2:$N$27>25)*($N$2:$N$27<=50))
J2:J5J2=FILTER($M2:$M27,($N$2:$N$27>50)*($N$2:$N$27<=75))
K2:K14K2=FILTER($M2:$M27,($N$2:$N$27>=75)*($N$2:$N$27<=100))
Dynamic array formulas.


This could be simplified a bit with the LET function.
 
Upvote 0
Is this what you are trying to do.
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQR
1
2
3
4
5
6SuiteTenantSq. Ft.Lease Expiration/StartJan-22Feb-22Mar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22AnnualComments
7100JPMorgan Chase925346387925392539253925392539253925392539253925392539253
81005Qingfeng Tao, an Individual91145230911911911911911911911911911911911911
9101, 202, 220, 400, 805, GARAGE1World Cinema, Inc.428530428534285342853428534285342853428534285342853428534285342853
101010S, 250Ersa Grae Corporation29240292429242924292429242924000000
11105Harvest Cafe116145565116111611161116111611161116111611161116111611161
121060Offshore Contract Services, LLC325345107325332533253325332533253325332533253325332533253
13200Travis Davis146245199146214621462146214621462146214621462146214621462
14310United Concordia Companies, Inc.122245138122212221222122212221222122212221222122212221222
15320CompuTrain Business Solutions, Ltd.317645657317631763176317631763176317631763176317631763176
16600EN Engineering, LLC1142747907114271142711427114271142711427114271142711427114271142711427
17700KRBE Lico, Inc.1852145535185211852118521185211852118521185211852118521185211852118521
18701Briggs & Caldwell, L.P.202044926202020202020202020202020202020202020202020202020
19803Precision Task Group, Inc.634245504634263426342634263426342634263426342634263426342
20920Leidos c/o Jones Lang LaSalle Americas779445443779477947794779477947794779477947794779477947794
21SuiteTenant1Lease Expiration111111111111
22SuiteTenant1Lease Expiration111111111111
23701Briggs & Caldwell, L.P.202044743000000202020202020202020202020
241100VACANT1214644562121461214612146121461214612146121461214612146121461214612146
25211VACANT109444593010941094109410941094109410941094109410941094
26
List
Cell Formulas
RangeFormula
A7:P25A7=LET(Rngs,(AA7#,AS7#),Rws,MAX(ROWS(AA7#),ROWS(AS7#)),Seq,SEQUENCE(Rws*AREAS(Rngs),,0),ary,IFERROR(INDEX(Rngs,MOD(Seq,Rws)+1,Seq*0+SEQUENCE(,COLUMNS(AA7#)),INT(Seq/Rws)+1),""),FILTER(ary,INDEX(ary,,1)<>""))
Dynamic array formulas.
 
Upvote 0
Solution
Let's say you've got 3 dynamic arrays, each consisting of 2 columns and a variable number of cells: three, four, and five. To make this formula more readable, let's create a named formula
Code: 
=SEQUENCE(ROWS(three)+ROWS(four)+ROWS(five))
.
To create a new array that vertically stacks these 3 dynamic arrays, use the formula below. You can wrap them with SORT and UNIQUE if you like

Excel Formula: 
=IFS(
combo<=ROWS(three),                           three,
combo<=ROWS(three)+ROWS(four),   INDEX(four,combo-ROWS(three),{1,2}),
TRUE,                                                          INDEX(five,combo-ROWS(three)-ROWS(four),{1,2})
)
 
Upvote 0
pde said:
Let's say you've got 3 dynamic arrays, each consisting of 2 columns and a variable number of cells: three, four, and five. To make this formula more readable, let's create a named formula
Code: 
=SEQUENCE(ROWS(three)+ROWS(four)+ROWS(five))
.
To create a new array that vertically stacks these 3 dynamic arrays, use the formula below. You can wrap them with SORT and UNIQUE if you like

Excel Formula: 
=IFS(
combo<=ROWS(three),                           three,
combo<=ROWS(three)+ROWS(four),   INDEX(four,combo-ROWS(three),{1,2}),
TRUE,                                                          INDEX(five,combo-ROWS(three)-ROWS(four),{1,2})
)
Click to expand...
PS...Forgot to mention that the combo is the named formula (
Excel Formula: 
=SEQUENCE(ROWS(three)+ROWS(four)+ROWS(five))
.[/CODE]). Also that I have to do this because VSTACK (and HSTACK) aren't available on my desktop Excel app, only on the online version, which is too clunky to use.
 
Upvote 0
Do you actually have a question, as this has absolutely nothing to do with the op?
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Dynamic Array Formula Problem
Replies
5
Views
457
KRice
K
W
  • Question Question
Defined coloring of dynamic diagrams
Replies
0
Views
404
WalmitAal
W
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
517
vampsthevampyre
V
S
  • Solved
Combine columns using dynamic array formula
Replies
3
Views
223
Fluff
Fluff
F
  • Solved
Complex Dynamic Array Cash Flow Spread - Returns Only $0
2
Replies
16
Views
637
felixstraube
F

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top