In a previous blog, I wrote about the ability to convert a list into a set of smaller lists (create a link to the WRAPCOLS blog). But what if you wanted to do the opposite? What if you wanted to merge a set of smaller ranges into a single range? Excel’s VSTACK and HSTACK formulas are an easy way to combine several non-contiguous ranges into one range.
The syntax of the VSTACK formula is:
=VSTACK(array1,array2,…)
- Each array parameter represents a range to be stacked
Using the following data as an example:
We can stack both ranges on top of each other using the following formula:
=VSTACK(A1:D4,A9:D11)
This example was relatively simple since both ranges have the same number of columns. However, if one range has less columns then each cell mismatch will generate an error. For example, taking all three columns of the first data set but only two columns from the second data set will generate this result:
=VSTACK(A1:D4,A9:B11)
We can remove the errors by nesting the VSTACK within an IFERROR formula:
=IFERROR(VSTACK(A1:D4,A9:B11),””)
We can also use the HSTACK formula if you prefer to append your data horizontally instead of vertically.
To combine both these tables into one table, this HSTACK formula will do the trick:
=HSTACK(A1:D4,G1:I4)
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Slow-Performing Scripts
IBM Planning Analytics Tips & Tricks: Converting a Manual Process to TurboIntegrator Scripts
IBM Planning Analytics Tips & Tricks: PAW Enter Members Feature
