Is there an alternative to VSTACK formula I can use?

[jvs411]

I need to use a function that extracts out info using FILTER but across multiple different row ranges at once. However, I just found out my Excel version doesn't support VSTACK... so now I'm kinda at a loss.

I know there's alternatives like LET function but that one just looks way too complicated for me to understand.

The excel formula I'm using is basically this
=FILTER(C10:CW56,C10:CW10=2)

where C10:CW56 is the table array I'm searching through and C10:CW20 is the row array I'm matching to the criteria, 2.

But I want to stack this formula ontop more formulas searching more different table arrays below. So my VSTACK presumably should look something like this:

=VSTACK(
=FILTER(C10:CW56,C10:CW10=2),
=FILTER(C59:CW105,C59:CW59=2),
=FILTER(C108:CW154,C108:CW108=2),
=FILTER(C157:CW203,C157:CW157=2),
)


But without VSTACK, is there an alternative formula I can use?

 

[Georgiboy]

Do you have any updates available for Office:

File / Account / Update Options

You may not be able to check if you are on a system at work, just thought i would ask.

 

[jvs411]

Yes, I'm using a work computer so I doubt I can update...

 

[Georgiboy]

I don't know what your data looks like so below is a guess at a solution:

Book1
	A	B	C	D	E	F	G	H
1	1	2	1	2			2	2
2	a	b	c	d			b	d
3	a	b	c	d			b	d
4	a	b	c	d			b	d
5	a	b	c	d			b	d
6							f	h
7							f	h
8	1	2	1	2			f	h
9	e	f	g	h			f	h
10	e	f	g	h			j	l
11	e	f	g	h			j	l
12	e	f	g	h			j	l
13							j	l
14
15	1	2	1	2
16	i	j	k	l
17	i	j	k	l
18	i	j	k	l
19	i	j	k	l
Sheet2

Cell Formulas
Range	Formula
G1:H1	G1	=FILTER(A1:D1,A1:D1=2)
G2:H13	G2	=FILTER(FILTER(A1:D19,NOT(ISNUMBER(A1:A19))*(A1:A19<>"")),A1:D1=2)
Dynamic array formulas.

 

[jvs411]

Thanks! Unfortunately that formula doesn't work in my case here. Sorry if I didn't give a better explanation earlier.

Basically my problem looks like this:

I'd like the formula to extract out the Wk2 tables only.
So the formula should be able produce this result:

Or this:

However, as you can see, the problem with my case is that the table arrays aren't all in the same 3 rows. I assume If I had VSTACK, I could easily just combine two FILTER formulas that searches through the table arrays B3:J5 and B7:J9 separately. Something like this?
VSTACK(
FILTER(B3:J5, B3:J3="Wk2")
FILTER(B7:J9, B7:J7="Wk2")
)


So is there a way to replicate this formula without VSTACK?

 

[johnnyL]

The following UDF is what I have saved in my toolbox for allowing older versions of Excel to use the 'VSTACK' function:

Function VSTACK(ParamArray ArrayAndNumber() As Variant) As Variant                                                      ' Excel 365
'
' =VSTACK(array1, [array2], ...)
'
' The first array or range to combine is mandatory.
' Additional arrays or ranges to combine are optional.
'
' Does not currently handle:
'   =VSTACK({"a","b"},AA2:AB2) = {"a","b";1,2}
'   =VSTACK({"a";"b"},AA2:AB2) = {"a",#N/A;"b",#N/A;1,2}
'
    Dim ArrayColumn         As Long, ArrayRow           As Long
    Dim ColumnIndex         As Long, ColIndex           As Long
    Dim TotalArrayColumns   As Long, TotalArrayRows     As Long
    Dim ResultArray         As Variant
'
    For ArrayRow = LBound(ArrayAndNumber) To UBound(ArrayAndNumber)                                                     ' Loop through the rows of ArrayAndNumber
        TotalArrayRows = TotalArrayRows + ArrayAndNumber(ArrayRow).rows.Count                                           '   Calculate TotalArrayRows
        TotalArrayColumns = Application.max(TotalArrayColumns, ArrayAndNumber(ArrayRow).columns.Count)                  '   Calculate TotalArrayColumns
    Next                                                                                                                ' Loop back
'
    ReDim ResultArray(1 To TotalArrayRows, 1 To TotalArrayColumns)                                                      ' Establish the row/column size of ResultArray
'
    ColumnIndex = 1                                                                                                     ' Initialize ColumnIndex
'
    For ArrayRow = LBound(ArrayAndNumber) To UBound(ArrayAndNumber)                                                     ' Loop through the rows of ArrayAndNumber
        For ArrayColumn = 1 To ArrayAndNumber(ArrayRow).rows.Count                                                      '   Loop
            For ColIndex = 1 To TotalArrayColumns                                                                       '       Loop through TotalArrayColumns
                ResultArray(ColumnIndex, ColIndex) = ArrayAndNumber(ArrayRow).Cells(ArrayColumn, ColIndex).value        '
            Next                                                                                                        '       Loop back
'
            ColumnIndex = ColumnIndex + 1                                                                               '       Increment ColumnIndex
        Next                                                                                                            '   Loop back
    Next                                                                                                                ' Loop back
'
    VSTACK = ResultArray                                                                                                '
End Function

It is not a complete replacement, but it is what I have come up with thus far.

 

[jvs411]

Okay, I've tried the custom formula after inserting it but this is what I got...

The formula gave a #VALUE! error instead.
However, I did verify the FILTER functions do work separately in Cell L2:
=FILTER(B3:J5, B3:J3="Wk2")
and Cell L6:
=FILTER(B7:J9, B7:J7="Wk2")

And I did a small test on the VSTACK function in Cell L11 and found it does work too: =VSTACK(G3:J5,B7:E9)

So is there something I'm still missing? Why doesn't this VSTACK formula work?
VSTACK(
FILTER(B3:J5, B3:J3="Wk2"),
FILTER(B7:J9, B7:J7="Wk2")
)

 

[RoryA]

The UDF works with ranges, not arrays.

 

[jvs411]

The UDF works with ranges, not arrays.

Oh, I guess I got to find a way to upgrade my Excel then...
Just to be clear, this limitation doesn't apply for the official VSTACK itself?

 

[RoryA]

No it doesn't. It's down to the way the UDF is coded.