Generate ID for cells containing same values

[Closed]
Report
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
Hello,
I am trying to generate the same IDs for cells containing same values and different IDs for cells containing different values

I have for example Column with names:
1. maria
2. maria
3. maria
4. joseph
5. john
6. joseph.

So I want that 1,2 and 3 become ID 1, 4 and 6, ID 2, and 5 ID 3 for example.
I got now a column list with unique values, but the ones repeated (as 2 or 3) are shown blank.
My excel has about 3000 rows, and I will be updating it every now and then. I do not want to use any function where values have to be alphabetically ordered or similar....
Is it possible?

Thanks is advance,
Cristina



1 reply

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Cristina,

The code below will do as requested.
The names are assumed to be in column A.
The ID's are assumed to be in column B.

Here is the code:
Sub RunMe()
Dim lRow, x As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1


For Each cell In Range("A2:A" & lRow)
    If cell.Offset(0, 1) = vbNullString Then
                
        With Range("A2:A" & lRow)
            Set c = .Find(cell.Value, LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Offset(0, 1).Value = x
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    
        x = x + 1
        
    End If
Next cell
        
End Sub


Best regards,
Trowa