NthMatch UDF as An Alternative to Array function...

Hi Folks,
Yesterday I posted following thread in this section ..
http://www.excelforum.com/tips-and-t...ift-enter.html
The thread was about using "Sumproduct as an alternative to array formulas.."
I was very happy that I made something like this..

but our Sir Mr. XOR opened my eyes and explain me why my formula was not that much fast as array function.. and was more intensive resource wise as well...

Then I started think about another alternative..
Actually I had, before posting the above thread, made an UDF ie NthMatch.. to return the Row No. of the Nth Match of a value in the array..
It is an improvement over Match function.. as Match function only returns row No. of the first Match...

when I got the solution.. what I saw that The NthMatch alternative was short(lenght wise) of the Sumproduct and Array alternatives..
So that is what I wanted to share with you all..
Have a visit at the link below.. and let me know whether there are any chances of improvement..
http://excel-buzz.blogspot.in/2014/0...-to-array.html

Here is the code for NthMatch(Lookup_value, Lookup_Range, Match No.)

Function NthMatch(ByVal Lvalue As Variant, ByVal Lrange As Variant, Mnum As Variant) As Integer
'*************Developed by Vikas Gautam ****************
'*************www.Excel-buzz.blogspot.com***************
Dim Arr()
ReDim Arr(0)
r = 0
If IsArray(Lrange) Then
    For Each cell In Lrange
        c = c + 1 'c counts row no. to be checked
        If cell = Lvalue Then
            ReDim Preserve Arr(r)
            Arr(r) = c 'assigning row no. of the match row to the Arr
            r = r + 1
        End If
    Next
Else
    If Lvalue = Lrange Then Arr(0) = 1 'if the Lrange is a single value ....
End If
NthMatch = Arr(Mnum - 1)
End Function

View post #9 for better solution given by KVS

Regards,
Vikas Gautam

Improved A bit..

Now It jump over the result when Match No. matches the row no....
means It stops looping when Nth Match is found.. and throws the result...

Here is the code..

Function NthMatch(ByVal Lvalue As Variant, ByVal Lrange As Variant, Mnum As Variant) As Integer
'*************Developed by Vikas Gautam ****************
'*************www.Excel-buzz.blogspot.com***************
Dim Arr()
ReDim Arr(0)
r = 0
If IsArray(Lrange) Then
    For Each cell In Lrange
        c = c + 1 'c counts row no. to be checked
        If cell = Lvalue Then
            ReDim Preserve Arr(r)
            Arr(r) = c 'assigning row no. of the match row to the Arr
            If r = Mnum - 1 Then NthMatch = Arr(Mnum - 1): Exit Function 'Stops looping when Nth Match is found..
            r = r + 1
        End If
    Next
Else
    If Lvalue = Lrange Then Arr(0) = 1 'if the Lrange is a single value ....
End If
NthMatch = Arr(Mnum - 1)
End Function

If there are chances of improvement then don't hesitate.. Have a comment..

My main criticism is that your use of the For each ... next loop will cause the code to search each and every entry for a match. It can be an effective "brute force" algorithm, and may be necessary for some situations. I would consider including, similar to Excel's built in lookup functions, a "range_lookup" argument. If the user will provide a sorted list -- then you can use a much more efficient binary search algorithm (http://en.wikipedia.org/wiki/Binary_search_algorithm ). You also only need to find the first and last match -- recognizing that, if the list is properly sorted, all matches will be in between the first and last match. I could see potential for significant performance improvement if you allow for (or even encourage) the search on a sorted list.

Gregor Y,
I really appreciate the work you have done..
But I think you have not seen one given by KVS.. ies

Function NthMatchNEW(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
'For ARRAY and RANGE input
Dim M As Variant
Dim X As Integer, MatchNo As Integer

On Error Resume Next

M = Join(Application.Transpose(Lrange), "&,")
M = Join(Lrange, "&,")
M = "&," & M & "&,"
X = 1

For t = 1 To Mnum
    X = InStr(X, M, "&," & Lvalue & "&,")
    If X < y Then GoTo Line1
    X = X + 1
    y = X
Next t

NthMatchNEW = Int((Len(Left(M, y)) - Len(Replace(Left(M, y), "&,", ""))) / 2)

Line1:
If NthMatchNEW = 0 Then NthMatchNEW = "#ERROR"

End Function

The above code is for RANGE and ARRAY as well..
It uses fantastic string manipulation.. very very fast..
Try it for your performance test
here is one more link for performance appraisals..
Improving performance in excel
There are some codes for speed testing, must try..

Good Luck..

Thanks for the suggestion Gregor y
I guess you got what you were looking for..

Hi KVS and all Experts.
Have a look, NthMatch Redefined

Function NthMatch(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|") & "|"
    Start = 1
    
    For c = 1 To Mnum
        Found = InStr(Start, Mstr, Lvalue)
        If Found = 0 Then NthMatch = "#N/A": End
        Start = Found + 1
    Next
    
    Temp = Left(Mstr, Found)
    RowNo = Len(Temp) - Len(Replace(Temp, "|", ""))
    NthMatch = RowNo
        
End Function

Note:- The file doen't contain the same code. So Copy and paste from here.
Check the attached file:-

Here is another new way,
may be I have reinvented.
This time NthMatchM with same set of parameters.

Function NthMatchM(ByVal Lvalue As Variant, ByRef Lrange As Variant, ByRef Mnum As Variant)
    Dim Arr As Variant
    
    Arr = Lrange 'Passing Lrange to other variable
    For r = 1 To Mnum
        RowNo = Application.Match(Lvalue, Arr, 0)
        If IsError(RowNo) Then NthMatchM = "#N/A": End
        Arr(RowNo, 1) = "##Ex##" 'Match excluded
    Next

    NthMatchM = RowNo
    
End Function

Note:- I have tested the speed of this code, and its much slower than above one.

Pl see the file.
Try this code also for speed test.

Function NthMatch1(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|") & "|"
    Start = 1
    
    For c = 1 To Mnum
    Found1 = InStr(Start, Mstr, "|" & Left(Lvalue, 1))
        If Found1 > 0 Then
        Start = Found1 + 1
            If Mid(Mstr, Start, Len(Lvalue) + 1) = Lvalue & "|" Then
            Start = Found1 + Len(Lvalue)
            Else
            c = c - 1
            End If
        
        End If
    Next
    
    Temp = Left(Mstr, Found1)
    NthMatch1 = Len(Temp) - Len(Replace(Temp, "|", ""))
        
End Function

Hi KVS Sir,
I acknowledge your code.
But I tested both and Mine is 3 secs faster than yours. ( with due respect )
your was 9 secs.
mine was 6 sec
I have run both five times.
The Speed may vary PC to PC.
I am attaching the file having both the Codes.
In sheet 2 I have arranged the test.
2500 rows of data.
Just select the formula range and run the RangeTimer code in the macro.
May be you have other ideas so test in your way.

Hi vikas
I have tested and found mine was slow. Now I have changed code.Pl check with attached file.

Hi KVS Sir,
There is a problem with your code (When I removed last 500 rows of data from reference),
Pl have a look at Row 670 for explanation.

I acknowledge the fact that you have the speed a lot by removing unnecessary steps in the code.
So, I believe the Final version should be.

Function NthMatch(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|") & "|"
    Start = 1
    
    For c = 1 To Mnum
        Start = InStr(Start + 1, Mstr, Lvalue)
        If Start = 0 Then End '<--- This the line which I have included to leave loop.
    Next
    
    Temp = Left(Mstr, Start)
    NthMatch = Len(Temp) - Len(Replace(Temp, "|", "")) '<-- Calculating Row No.
End Function

Hi KVS Sir,
There is a problem with your code (When I removed last 500 rows of data from reference),
Pl have a look at Row 670 for explanation.

I acknowledge the fact that you have improved the speed a lot by removing unnecessary steps in the code.
So, I believe the Final version should be.

Function NthMatch(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|") & "|"
    Start = 1
    
    For c = 1 To Mnum
        Start = InStr(Start + 1, Mstr, Lvalue)
        If Start = 0 Then End '<--- This the line which I have included to leave loop.
    Next
    
    Temp = Left(Mstr, Start)
    NthMatch = Len(Temp) - Len(Replace(Temp, "|", "")) '<-- Calculating Row No.
End Function

Yes, KVS Sir,
Your is faster but I didn't understand how.
However, I have come up with one more technique.
Here is it.

Function NthMatchE(ByVal Lvalue As Variant, ByVal Lrange As Variant, Mnum As Variant)
f = "SMALL(IF(" & Lrange.Address & "=""" & Lvalue & """,ROW($1:" & Lrange.Rows.Count & "))," & Mnum & ")"
NthMatchE = Evaluate(f)
End Function

Because when I tried the array construction, it was faster than every other technique.
So I tried to copy the same behavior using Evaluate Function.
Yours took 4.15 secs but it takes only 2 sec. (would be more fast on your PC)
Try it and pls share the results.

Here is the Code with less words but matches ur speed:-

Function NthMatch(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|")
    Lvalue = "|" & Lvalue
    Start = 0
    
    For c = 1 To Mnum
        Start = InStr(Start + 1, Mstr, Lvalue)
        If Start = 0 Then NthMatch = "#N/A": GoTo 0 '<--- This the line which I have included to leave loop.
    Next

    Temp = Left(Mstr, Start)
    NthMatch = Len(Temp) - Len(Replace(Temp, "|", "")) '<-- Calculating Row No.
0
End Function

Yes I agree sir.
But I have learnt a lot from what we have done so far.
Thanks for your cooperation.