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?

 

[RoryA]

I haven't done any real testing, but try something like this:

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 RowIndex         As Long, ColIndex           As Long
    Dim TotalArrayColumns   As Long, TotalArrayRows     As Long
    Dim ResultArray         As Variant
'
   Dim dataSet
    For ArrayRow = LBound(ArrayAndNumber) To UBound(ArrayAndNumber)                                                     ' Loop through the rows of ArrayAndNumber
        dataSet = GetArray(ArrayAndNumber(ArrayRow))
        TotalArrayRows = TotalArrayRows + UBound(dataSet, 1)                                        '   Calculate TotalArrayRows
        TotalArrayColumns = Application.Max(TotalArrayColumns, UBound(dataSet, 2))                  '   Calculate TotalArrayColumns
    Next                                                                                                                ' Loop back
'
    ReDim ResultArray(1 To TotalArrayRows, 1 To TotalArrayColumns)                                                      ' Establish the row/column size of ResultArray
'
    RowIndex = 1                                                                                                     ' Initialize ColumnIndex
'
    For ArrayRow = LBound(ArrayAndNumber) To UBound(ArrayAndNumber)                                                     ' Loop through the rows of ArrayAndNumber
        dataSet = GetArray(ArrayAndNumber(ArrayRow))
        For ArrayColumn = LBound(dataSet, 1) To UBound(dataSet, 1)                                                      '   Loop
            For ColIndex = LBound(dataSet, 2) To UBound(dataSet, 2)                                                                       '       Loop through TotalArrayColumns
                ResultArray(RowIndex, ColIndex) = dataSet(ArrayColumn, ColIndex)        '
            Next                                                                                                        '       Loop back
'
            RowIndex = RowIndex + 1                                                                               '       Increment ColumnIndex
        Next                                                                                                            '   Loop back
    Next                                                                                                                ' Loop back
'
    VSTACK = ResultArray                                                                                                '
End Function
Function GetArray(InputVariable) As Variant()
   Dim dataSet()
   If TypeName(InputVariable) = "Range" Then
      If InputVariable.Count = 1 Then
         ReDim dataSet(1 To 1, 1 To 1)
         dataSet(1, 1) = InputVariable.Value2
      Else
         dataSet = InputVariable.Value2
      End If
   Else
      If VarType(InputVariable) And vbArray = vbArray Then
         dataSet = Make2DArray(InputVariable)
      Else
         ReDim dataSet(1 To 1, 1 To 1)
         dataSet(1, 1) = InputVariable
      End If
   End If
   GetArray = dataSet
End Function
Function Make2DArray(InputArr As Variant) As Variant()
   Dim Is2Darray As Boolean
   On Error Resume Next
   Is2Darray = UBound(InputArr, 2) >= 0
   On Error GoTo 0
   If Is2Darray Then
      Make2DArray = InputArr
   Else
      Dim tempArray()
      Dim offset As Long
   
      ReDim tempArray(1 To 1, 1 To UBound(InputArr, 1) - LBound(InputArr) + 1)
      Dim x As Long, y As Long
      y = 1
      For x = LBound(InputArr) To UBound(InputArr)
         tempArray(1, y) = InputArr(x)
         y = y + 1
      Next
      Make2DArray = tempArray
   End If
End Function

 

[Georgiboy]

If you have VSTACK then i don't understand why it is not working.

If you paste the below into your Excel, do you get errors?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	1	1	1	1		2	2	2	2		2	2	2	2
4	a	a	a	a		b	b	b	b		b	b	b	b
5	a	a	a	a		b	b	b	b		b	b	b	b
6
7	2	2	2	2		3	3	3	3		2	2	2	2
8	c	c	c	c		d	d	d	d		c	c	c	c
9	c	c	c	c		d	d	d	d		c	c	c	c
10
11
12											2	2	2	2
13											b	b	b	b
14											b	b	b	b
15											2	2	2	2
16											c	c	c	c
17											c	c	c	c
Sheet1

Cell Formulas
Range	Formula
L3:O9	L3	=VSTACK(FILTER(B3:J5,B3:J3=2),{"","","",""},FILTER(B7:J9,B7:J7=2))
L12:O17	L12	=VSTACK(FILTER(B3:J5,B3:J3=2),FILTER(B7:J9,B7:J7=2))
Dynamic array formulas.

 

[jvs411]

If you have VSTACK then i don't understand why it is not working.

If you paste the below into your Excel, do you get errors?

No, I don't have VSTACK. I just used the unofficial UDF that RoryA gave me.

 

[jvs411]

Anyway I finally found an interim solution which is to just use Microsoft Excel online instead... which has the VSTACK available finally.

 

[Georgiboy]

Sorry I misunderstood, glad you found a solution.

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help for a replacement formula to VSTACK!
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.