I have a function to filter a 2-d array.
It works. But I am not sure if the following mechanism is a sensible concept.
The idea is:
loop over the input array which is received as
input_arr(y,x)
store the values in
temp_arr(x,y)
, since in order to resize the y-dimension it needs to be last dimension, so I have to transpose the values as I store themonce the loop is done, transpose
temp_arr(x,y)
which becomes the return valuePublic Function filter_arr(input_arr As Variant, col_index As Long, filter_value As Variant) As Variant 'the input_arr might be indexed starting from 0 or 1 Dim n1 As Long, n2 As Long n1 = LBound(input_arr, 2) n2 = UBound(input_arr, 2) Dim temp_arr() As Variant Dim y As Long, x As Long, count As Long count = 0 If (LBound(input_arr, 1) = 0) Then For y = LBound(input_arr, 1) To UBound(input_arr, 1) If (input_arr(y, col_index) = filter_value) Then ReDim Preserve temp_arr(n1 To n2, 0 To count) For x = n1 To n2 temp_arr(x, count) = input_arr(y, x) Next x count = count + 1 End If Next y Else 'if LBound(input_arr, 1) = 1 For y = LBound(input_arr, 1) To UBound(input_arr, 1) If (input_arr(y, col_index) = filter_value) Then count = count + 1 ReDim Preserve temp_arr(n1 To n2, 1 To count) For x = n1 To n2 temp_arr(x, count) = input_arr(y, x) Next x End If Next y End If filter_arr = Application.Transpose(temp_arr) End Function
Edit: adding some context
Imagine that I have a ws
in an .xlam
file that contains product information which I've added as an add-in.
I have a function like =getProductData()
which returns an array containing that data
So excel has a SORT
function which works great with this kind of thing. I can do =SORT(getProductData(), 2, 1)
to return my product data sorted by the 2nd column
But excel's FILTER
function needs me to specify the data itself as its second parameter, not just its index, like in SORT
I can use INDEX
to do something like =SORT(FILTER(getProductData(),INDEX(getProductData(),,3)="red",""),2,1)
to filter my product data by the 3rd column if the value = red
, then sort by the 2nd column of the resulting array
But I was hoping to do something like
=SORT(filter_arr(getProductData(),3,"red"),2,1)
to do the same
Naturally I'm still just playing around to determine what I can pull off - maybe using INDEX
is the way to go and I'm trying to reinvent the wheel
=LET(array,getProductData(),FILTER(array,INDEX(array,,3)="red"))
. Or better (but requires beta channel):FILTERIF = LAMBDA(array,filter_index,include_if,FILTER(array,INDEX(array,,filter_index) = include_if))
called like=FILTERIF(getProductData(),3,"red")
\$\endgroup\$Let
andLambda
. In any case, not every version of Excel supports them. \$\endgroup\$