Counter for tumblr
Website Stats

Find nth occurrence in a column

A solution to  find Nth occurence in a column coupled with INDEX/ MATCH function. 

Step 1: In Excel, Enable "Developer" tab in Excel:  

Pathway (for Mac): Preference--> Ribbons--> select "Developer" 

Step 2: Insert Macro 

Pathway: Develop--> Editor--> Insert module (copy the green text below)  

Function NthInstance(F As String, R As Range, N As Long) As String
Dim vA As Variant
vA = R.Value
For I = LBound(vA, 1) To UBound(vA, 1)
    If InStr(1, vA(I, 1), F) > 0 Then
        ct = ct + 1
        If ct = N Then
            NthInstance = R.Cells(I, 1).Address(1, 1)
          Exit Function
        End If
    End If
Next I
If ct < N Then NthInstance = "Only " & ct & " instances found"
End Function
 
Credit:  source of the VBA code 
http://www.mrexcel.com/forum/excel-questions/678792-find-nth-occurrence-column.html
 
Step 3: INDEX/INDIRECT function can then be applied to find values of Nth occurencies. 

e.g.

=INDEX($F$1:$H$10,ROW(INDIRECT(NthInstance($F29,$F$2:$F$10,$E29))),2)

Download an example sample here
 
Enjoy. 
 
Posted by Xun Liao on Friday 4 September 2015 at 14:39
Comments
RSS