Have you ever needed to modify a list in Excel without wanting to manually manipulate the data? Excel’s WRAPCOLS formula lets you reshape data without having to perform tedious copy and pasting.
The syntax of the WRAPCOLS formula is:
=WRAPCOLS(vector, wrap_count, pad_with)
- Vector: the data to be modified
- Wrap_count: the number of rows per group
- Pad_width: the data to fill in for missing values
For example, suppose you have a list of 10 people that you want to split into three groups.
The manual way of copy/pasting would work for a small list like this, but this approach would not be efficient for larger lists.
To make a set of three groups, you can use this formula:
Excel will then determine how to fill the groups specified. However, the remaining blank cells appear as #N/A which may cause some user confusion. You can remove the #N/A data cells by using the pad width parameter. To display blanks instead of #N/A, the formula would change to:
This approach will allow you to convert a long list into a more easily readable set of smaller list, thereby making it easier to analyze the information.
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: Fixing Repetitive Scripts
IBM Planning Analytics Tips & Tricks: PAW Border Lines
IBM Planning Analytics Tips & Tricks: How We Solve Problems
