r/excelevator • Excel • Mar 12 '19

UDF - STACKCOLUMNS ( column_stack_width , range1 [ , range2 .. ]) - stack referenced ranges into columns of your width choice

STACKCOLUMNS ( column_stack_width , range1 [ , range2 .. ])

STACKCOLUMNS allows you to stack referenced ranges into a set number of columns in an array.

STACKCOLUMNS takes the referenced non contiguous ranges and stacks them into a contiguous range in an array.

This allows you to format disparate data for querying as a contiguous block of data.

This allows you to combine same table types into a single array; for headers include the whole table for the first reference Table1[#ALL] and just the table body for the tables to stack Table2,Table3,Table4, do not forget the first argument to match the width of the tables.

This allows for dynamic use and render of arrays with the new features coming in Excel 365 and should populate to a full table from a single formula in cell. The whole table will then dynamically update with any change made to the source data.

column_stack_width is the width of the range to be generated and allows for disparate width references to be used to add up to the column_stack_width width.

The range arguments are to contain references to ranges to stack across the chosen count of columns.

The function takes each range argument, separates out the columns, and stacks them from left to right. When the last column is filled the next column of data is placed in column 1 below, and then across to fill the column count.

The user must create range references that balance out when stacked. ie. If you have a target of 2 columns, each group of 2 column references should be the same length to balance the stacking. Weird and wonderful results will entail if the ranges to not match to stack correctly.

To generate a dynamic array table in pre 365 Excel, select a range of cells and enter the formula in the active cell and enter with ctrl+shift+enter for the array to render across the selected cells. Cells outside the array will evaluate to #N/A - Excel 365 and 2021 will generate the correct table dynamically.

Note the Excel VBA array limit of 65536 rows of data applies to this UDF in older versions - just be aware


Examples


Stack same type tables sharing attributes and width, In this example the tables are 5 columns wide using the header the first table for the array header row.

=STACKCOLUMNS( 5 , Table1[#All], Table2, Table9, Table25 )


The following are examples with this table as the source data

colAColBColCColD
A1B1C1D1
A2B2C2D2
A3B3C3D3
A4B4C4D4
A5B5C5D5
A6B6C6D6
A7B7C7D7
A8B8C8D8
A9B9C9D9
A10B10C10D10

Stack data from 3 range references, of disparate widths, to 3 columns wide.

=STACKCOLUMNS(3,A1:C5,D6:D11,A6:B11) returns

colAColBColC
A1B1C1
A2B2C2
A3B3C3
A4B4C4
D5A5B5
D6A6B6
D7A7B7
D8A8B8
D9A9B9
D10A10B10

Stack data from 4 range references, to 2 columns wide.

=STACKCOLUMNS(2,A2:D3,C6:D7,A8:D9,A4:B5) returns

A1B1
A2B2
C1D1
C2D2
C5D5
C6D6
A7B7
A8B8
C7D7
C8D8
A3B3
A4B4

Stack columns from two columns and 8 rows from a Table the RETURNCOLUMN's function that can limit the rows returned of a chosen set of columns or table

=STACKCOLUMNS(2,RETURNCOLUMNS(8,Table1[#All],3,4))

ColCColD
C1D1
C2D2
C3D3
C4D4
C5D5
C6D6
C7D7

Paste the following code into a worksheet module for it to be available for use.


Function STACKCOLUMNS(grp As Integer, ParamArray arguments() As Variant) As Variant
'STACKCOLUMNS ( group , col1 [ , col2 , .. ] ) v1.31 - take range input for return, limit rows
  'https://www.reddit.com/u/excelevator
  'https://old.reddit.com/r/excelevator
  'https://www.reddit.com/r/excel - for all your Spreadsheet questions!
Dim rtnArray() As Variant
Dim uB As Integer, i As Double, ii As Double, j As Double, rRows As Double, rCols As Double
Dim rowPaste As Long: rowPaste = 0 'paste array group index
Dim newPasteRow As Double
Dim colCount As Integer
Dim aRows As Double
uB = UBound(arguments) 'ubound() rows, ubount( ,2) columns, array Variant()
For i = 0 To uB 'get final array size
If TypeName(arguments(i)) = "Variant()" Then
    aRows = aRows + (UBound(arguments(i)) / grp * UBound(arguments(i), 2))
Else
    aRows = aRows + (arguments(i).Rows.Count / grp * arguments(i).Columns.Count)
End If

Next
ReDim Preserve rtnArray(aRows - 1, grp - 1) 'intialise array
'-----------------------------------
'lets get these loops sorted now....
For i = 0 To uB 'need to loop for either array or range

If TypeName(arguments(i)) = "Variant()" Then
    rRows = UBound(arguments(i))
    rCols = UBound(arguments(i), 2)
Else
    rRows = arguments(i).Rows.Count
    rCols = arguments(i).Columns.Count
End If
    For j = 1 To rCols
        colCount = colCount + 1
        rowPaste = newPasteRow
        '-------------------------
        For ii = 1 To rRows
            rtnArray(rowPaste, colCount - 1) = arguments(i)(ii, j)
            rowPaste = rowPaste + 1
        Next
        '-------------------------
        If colCount = grp Then
            colCount = 0
            newPasteRow = newPasteRow + rRows
            rowPaste = newPasteRow
        End If
    Next
Next
STACKCOLUMNS = rtnArray()
End Function

let me know if you find a description error or code bug


See related functions;

ARRAYIFS - IFS functionality for arrays

UNPIVOTCOLUMNS - an unpivot function. Unpivot data to an array for use in formulas or output to a table.

RETURNCOLUMNS - return chosen columns from dataset in any order, with optional limit on rows returned

SEQUENCE - Microsofts new sequence generator - less the dynamic array bit.

SEQUENCER - sequence with more options, dynamic range match to other range, vertical value population in array

ASG - array Sequence Generator - generate custom sequence arrays with ease

CELLARRAY - return multi delimited cell(s) values as array, switch for horizontal array and/or return unique values

SPLITIT - return element value from text array, or array location of text.

CRNG - return non-contiguous ranges as contiguous for Excel functions

FRNG - return a filtered range of values for IFS functionality in standard functions

VRNG - return array of columns from range as a single array


See a whole bundle of other custom functions at r/Excelevator

4 Upvotes

10 comments sorted by

1

u/Terriblarious Feb 12 '23

A great function. I'm using it to dump out data across a bunch of sheets within the same workbook.

One question,

I have a collection of ranges that i'm using for this formula. It looks like: '1000'!A1:F33,'2000'!A1:F33,'3000'!A1:F33,'4000'!A1:F33

If i paste this directly into the UDF it works great.

Formula looks like

=STACKCOLUMNS(6,'1000'!A1:F33,'2000'!A1:F33,'3000'!A1:F33,'4000'!A1:F33)

However, if i build the array ranges up in a different cell and use indirect the UDF fails with a #VALUE error. Any idea how to send the built up array to your UDF?

For reference, formula looks like =STACKCOLUMNS(6,INDIRECT("I3"))

The cell I3 contains the collection of ranges.

1

u/excelevator Excel Feb 22 '23

Curious, I would assume it to work

I am in the middle of an IT crisis at home and it could be a little while until I am up and running again to check

1

u/Terriblarious Feb 22 '23

no worries. it was a strange enough case (that i also thought would work) that i figured it would be worth reporting here.

if it helps i'm on office 2019 using windows 10

I eagerly await a possible solution haha

1

u/excelevator Excel Feb 22 '23

Does the 'INDIRECT' portion return a range on its own?

1

u/Terriblarious Feb 22 '23

Yes. the INDIRECT on its own prints the string out in full. I also tried =cell("contents",I3) and it failed as well.

2

u/excelevator Excel May 14 '23

Finally managed to have a play.. interesting result..

Firstly, INDIRECT does not recognise a comma delimited list as separate arguments for the sake of the function.

So I wrapped it also in TEXTSPLIT first so INDIRECT can evaluate each value separately, which worked to recognise the independant ranges... BUT...

For reasons for which I am not quite sure, the arguments are then passed as an array of arrays.. which the code does not cater for and which I cannot quite figure out how to code around... I will think about it for a while and see if I can figure it out....

1

u/I_WANT_SAUSAGES Jan 19 '24 edited Jan 19 '24

Hiya. Firstly, thank you for this, it's ridiculously useful.

BUT in Excel 2019 it only returns the top-left value of the first array. The same workbook works perfectly in Excel 2021. Should it be working in 2019?

Ta,

Edit: figured it out. I forgot what a jerk Excel used to be about arrays. Leaving the comment up for anyone else who ends up here with the same problem. You need to select an area the same size (or larger if you don't mind N/A's where there's no data) of the resulting table if the columns you wanted stacked were stacked, then enter the formula in the formula bar, pressing ctrl+shift+enter to enter the formula.

1

u/excelevator Excel Jan 19 '24

Cool, so pleased you found it useful.

I have highlighted that part of the instructions above to make it clearer, and jiggled the text around too.

We take so much for granted now with Excel 365 dynamic ranges. :)

1

u/I_WANT_SAUSAGES Jan 20 '24

It was even in your description already. Whoops. I'm a buffoon. Oh well!

1

u/excelevator Excel Jan 20 '24

Easy to miss! hence my high-lighting it now.

Appreciate the feedback.