Stack "N" columns on top of each other

nparrillo

New Member
Joined
Mar 27, 2023
Messages
29
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
See my attached example. I want to stack every 5th column on top of each other using this dumby example.

Thank you,
 

Attachments

  • Stack Formula.jpg
    Stack Formula.jpg
    156.4 KB · Views: 9

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
N.B. You can post an extract of your sheet to the forum with the forum's tool named XL2BB.

Try VStack
 
Upvote 0
Does this work?:

Libro2
ABCDEFGHIJKLMNOPQRS
1Home CatsDogsMiceHome CatsDogsMiceHome CatsDogsMiceHome CatsDogsMice
2Home 1631Home 1631Home 2291Home 36323
3Home 1165Home 1165Home 2162Home 3926
4Home 1724Home 1724Home 2444Home 3555
5Home 1945Home 1945Home 2846Home 3716
6Home 1164Home 1164Home 3384
7Home 2291Home 3291
8Home 2162Home 3421
9Home 2444Home 3421
10Home 2846
11Home 36323
12Home 3926
13Home 3555
14Home 3716
15Home 3384
16Home 3291
17Home 3421
18Home 3421
Hoja1
Cell Formulas
RangeFormula
A2:D18A2=VSTACK(F2:I6,K2:N5,P2:S9)
Dynamic array formulas.
 
Upvote 0
Does this work?:

Libro2
ABCDEFGHIJKLMNOPQRS
1Home CatsDogsMiceHome CatsDogsMiceHome CatsDogsMiceHome CatsDogsMice
2Home 1631Home 1631Home 2291Home 36323
3Home 1165Home 1165Home 2162Home 3926
4Home 1724Home 1724Home 2444Home 3555
5Home 1945Home 1945Home 2846Home 3716
6Home 1164Home 1164Home 3384
7Home 2291Home 3291
8Home 2162Home 3421
9Home 2444Home 3421
10Home 2846
11Home 36323
12Home 3926
13Home 3555
14Home 3716
15Home 3384
16Home 3291
17Home 3421
18Home 3421
Hoja1
Cell Formulas
RangeFormula
A2:D18A2=VSTACK(F2:I6,K2:N5,P2:S9)
Dynamic array formulas.
That wont work bc I will have hundreds of additional "Houses" that are not in this dumby data set.
 
Upvote 0
Just update the ranges. Or you could convert each dataset into a table, an reference the tabledata. When you add more rows to a table it updates automatically.

Libro2
ABCDEFGHIJKLMNOPQRS
1Home CatsDogsMiceHome CatsDogsMiceHome CatsDogsMiceHome CatsDogsMice
2Home 1631Home 1631Home 2291Home 36323
3Home 1165Home 1165Home 2162Home 3926
4Home 1724Home 1724Home 2444Home 3555
5Home 1945Home 1945Home 2846Home 3716
6Home 1164Home 1164Home 3384
7Home 2291Home 3291
8Home 2162Home 3421
9Home 2444Home 3421
10Home 2846
11Home 36323
12Home 3926
13Home 3555
14Home 3716
15Home 3384
16Home 3291
17Home 3421
18Home 3421
Hoja1
Cell Formulas
RangeFormula
A2:D18A2=VSTACK(Tabla1,Tabla2,Tabla3)
Dynamic array formulas.


it automatically updates to:

Libro2
ABCDEFGHIJKLMNOPQRS
1Home CatsDogsMiceHome CatsDogsMiceHome CatsDogsMiceHome CatsDogsMice
2Home 1631Home 1631Home 2291Home 36323
3Home 1165Home 1165Home 2162Home 3926
4Home 1724Home 1724Home 2444Home 3555
5Home 1945Home 1945Home 2846Home 3716
6Home 1164Home 1164Home 2291Home 3384
7Home 1631Home 1631Home 2162Home 3291
8Home 1165Home 1165Home 2444Home 3421
9Home 1724Home 1724Home 2846Home 3421
10Home 1945Home 1945
11Home 1164Home 1164
12Home 1631Home 1631
13Home 1165Home 1165
14Home 1724Home 1724
15Home 1945Home 1945
16Home 1164Home 1164
17Home 2291
18Home 2162
19Home 2444
20Home 2846
21Home 2291
22Home 2162
23Home 2444
24Home 2846
25Home 36323
26Home 3926
27Home 3555
28Home 3716
29Home 3384
30Home 3291
31Home 3421
32Home 3421
Hoja1
Cell Formulas
RangeFormula
A2:D32A2=VSTACK(Tabla1,Tabla2,Tabla3)
Dynamic array formulas.
 
Upvote 0
How about
Excel Formula:
=WRAPROWS(TOCOL(CHOOSECOLS(F2:S200,1,2,3,4,6,7,8,9,11,12,13,14),1),4)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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