Generate ID for cells containing same valuesClosed

Question

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



Configuration: Windows / Chrome 42.0.2311.90

TrowaD · Answer

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

Generate ID for cells containing same values

1 response

Similar discussions

Subscribe To Our Newsletter!