Excel: how to create a number in a column accordin

Question

I have a text column with repeated values, for example:

   |    A    | 
---|---------|
 1 | emails  |
 2 |  foo    |
 3 |  foo    |
 4 |  bar    |
 5 |  bar    |
 6 |  stuff  |
 7 |  stuff  |
 8 |  stuff  |

What I would like to do is to have another column with numbers, so that each number matches the value in the first column, for example:

   |    A    |    B    | 
---|---------|---------|
 1 | emails  | number  |
 2 |  foo    |    1    |
 3 |  foo    |    1    |
 4 |  bar    |    2    |
 5 |  bar    |    2    |
 6 |  stuff  |    3    |
 7 |  stuff  |    3    |
 8 |  stuff  |    3    |

Answer 1

If I understand your question correctly, the data maybe sorted and needs to be numbered by the distinct values in column A, so "foo" is 1 and "bar" is 2, and so on for unique values in column A.

I would use VBA and a Dictionary to build the list:

Public Sub UpdateMapValues()
    Dim MappedValues As New Scripting.Dictionary
    Dim oSheet As Worksheet
    Dim Name As String
    Dim Index As Long
    Dim Number As Long
    Dim LastNumber As Long
    Set oSheet = ActiveSheet
    LastNumber = 0
    For Index = 2 To oSheet.UsedRange.Rows.Count
        Name = oSheet.Cells(Index, 1).Text
        If Not MappedValues.Exists(Name) Then
            LastNumber = LastNumber + 1
            Number = LastNumber
            Call MappedValues.Add(Name, Number)
        Else
            Number = MappedValues(Name)
        End If
        oSheet.Cells(Index, 2).Value = CStr(Number)
    Next Index
End Sub

This will update the number in column B. You could also do things like putting the cell reference (such as "A2" or "A5") in column B which you could use for other Excel functions.

In order to use Scripting.Dictionary, just add a reference in Tools\References and select Microsoft Scripting Runtime.