Unique increment ID for each distinct value in a range

debugcn Published at Dev

user1783736

I have a function (UDF) that creates a unique id for each distinct String value in a range. My problem is that I am using the row number for the firs occurrence found. I need: a unique ID that increments by one for each distinct value. Please take a look at the image of my current values and the actual values that I want:

enter image description here

I am using the the following formula in column B row 2:

 =insert_id1(A2,$A$2:$A$8)

Below is my UDF:

Function insert_id1(Search_string As String, Search_in_col As Range)
Dim i As Long
Dim result As Integer
result = 0

For i = 1 To Search_in_col.count

  If Search_in_col.Cells(i, 1) = Search_string Then
        If result = 0 Or result > Search_in_col.Cells(i, 1).Row Then
        result = Search_in_col.Cells(i, 1).Row
        End If
  End If

Next
insert_id1 = result
End Function

tigeravatar

Does it have to be VBA? Use this formula in cell B2 and copy down:

=IF(COUNTIF(A$1:A1, A2)=0,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,FALSE))

EDIT

If it really needs to be VBA/UDF:

Public Function insert_id1(Search_string As String, Search_in_col As Range) As Long

    Dim i As Long
    Dim sUnq As String
    Dim cel As Range

    For Each cel In Search_in_col
        If Len(cel.Text) > 0 _
        And InStr(1, "|" & sUnq & "|", "|" & cel.Text & "|", vbTextCompare) = 0 Then
            i = i + 1
            sUnq = sUnq & "|" & cel.Text
            If cel.Text = Search_string Then
                insert_id1 = i
                Exit Function
            End If
        End If
    Next cel

End Function

And then the formula becomes: