Cette page appartient aux archives web de l'EPFL et n'est plus tenue à jour.
This page belongs to EPFL's web archive and is no longer updated.

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 
Step 3: INDEX/INDIRECT function can then be applied to find values of Nth occurencies. 



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