UDF as an alternative for the new FILTER function built-in for the Excel 365

[omairhe]

Hey Excel Gurus,

How are you guys doing today? I need a big favor from you. I am using older version of Ms Excel and in it the newer FILTER function that works by default in Office 365 is missing. I was hoping if anyone can make a UDF that could work exactly same way as the original.

Thanks a lot and will appreciate..

 

[RoryA]

If you mean you want a UDF that can spill automatically, then you're pretty much out of luck.

 

[omairhe]

If you mean you want a UDF that can spill automatically, then you're pretty much out of luck.

my original post did imply I needed an exact replica. However, still, could I get the functionality of it without the spill?

For instance the user fill down the UDF on cells where it is required. manually filling the cells instead of spill.

I want to type something in cell say A1, and the UDF shall go in cell B1:B50
If A1 = Sub

Then B1 = Substitue
B2 = Submarine
B3 = Water Submerge
B4 = Subside
etc

Lookup array belongs to a table in other sheet of the same workbook. Example where all the "Sub" and non sub values are listed.

Is possible?

 

[RoryA]

This should get you started:

Function FilterRange(InputRange As Range, FilterFor As Variant)
    Dim data
    data = InputRange.Value
    Dim x As Long
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    For x = LBound(data) To UBound(data)
        If InStr(1, data(x, 1), FilterFor, vbTextCompare) <> 0 Then d(data(x, 1)) = Empty
    Next x
    If d.Count Then
        Dim outputArray()
        ReDim outputArray(1 To d.Count, 1 To 1)
        For x = 1 To d.Count
            outputArray(x, 1) = d.keys()(x - 1)
        Next x
        FilterRange = outputArray
    End If
End Function

 

[omairhe]

This should get you started:

Function FilterRange(InputRange As Range, FilterFor As Variant)
    Dim data
    data = InputRange.Value
    Dim x As Long
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    For x = LBound(data) To UBound(data)
        If InStr(1, data(x, 1), FilterFor, vbTextCompare) <> 0 Then d(data(x, 1)) = Empty
    Next x
    If d.Count Then
        Dim outputArray()
        ReDim outputArray(1 To d.Count, 1 To 1)
        For x = 1 To d.Count
            outputArray(x, 1) = d.keys()(x - 1)
        Next x
        FilterRange = outputArray
    End If
End Function

I tested the above but I get some repetitions.

for instance my lookup array in Sheet2 is..

Substitue

Intersub

manly

substance

And the formula in B1 of Sheet1 is =FilterRange(Sheet2!A2:A5,$A$2), it is copied down and I type in Cell A2 = man

The result will be

manly

manly

manly

When A2 = sub

Substitue

Intersub

substance

substance

substance appearing twice and manly appearing thrice... am I doing something wrong?

 

[RoryA]

You need to array enter the formula into multiple cells at once as it returns an array. Or wrap it in an index function:

=INDEX(FilterRange(Sheet2!A2:A5,$A$2),ROW(A1))

for example.

 

[omairhe]

You need to array enter the formula into multiple cells at once as it returns an array. Or wrap it in an index function:

=INDEX(FilterRange(Sheet2!A2:A5,$A$2),ROW(A1))

for example.

Do you mean pressing Ctrl+Shift+Enter? I tried it but the results are the same. I am a novice excel user and would appreciate a working excel file as a demo which I can download if not much trouble to you please...
Thanks for your help thus far.

 

[RoryA]

The INDEX version I just posted doesn't require array-entry, but you will of course want to use an absolute range reference for the source data:

=INDEX(FilterRange(Sheet2!$A$2:$A$5,$A$2),ROW(A1))

 

[omairhe]

The INDEX version I just posted doesn't require array-entry, but you will of course want to use an absolute range reference for the source data:

=INDEX(FilterRange(Sheet2!$A$2:$A$5,$A$2),ROW(A1))

was getting reference error in the end of the filtered list... hence with iferror it is working 100%

=IFERROR(INDEX(FilterRange(Sheet2!$A$2:$A$10,$A$2),ROW(A1)),"")

 

[Peter_SSs]

Looks like you are just dealing with a column of values so would this also work for you?
I've just done it on one sheet but you should be able to adapt to two if it meets your need.

Function Fltr(col As Range, FilterFor As String)
  Fltr = Filter(Application.Transpose(col), FilterFor, True, vbTextCompare)
End Function

omairhe 2020-02-24 1.xlsm
	A	B	C	D	E
1	Substitue		sub		Substitue
2	Intersub				Intersub
3	manly				substance
4	substance
5
Sheet2

Cell Formulas
Range	Formula
E1:E5	E1	=IFERROR(INDEX(Fltr(A$1:A$4,C$1),ROWS(E$1:E1)),"")