Excel: how to create a number in a column according to a text value in other column?

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

There are a number of ways of doing this, depending on the exact nature of your table of data.

If the repeated values in your text column are limited e.g. less than 10, then you could just hard code some nested if statements.

If(A1="mail1", 1, If(A1="mail2", 2, If(... etc.

I don't recommend this is as it is a bit clumsy, you can only have upto 7(?) nested IF statements, and not very re-usable if you want to use the code in another worksheet for different values.

If you know that the text stem is always the same length e.g. in your example "mail" is always the prefix and is 4 characters long then you can use the following formula:

RIGHT(A1,LEN(A2)-4) 'The "4" denotes the length of the string "mail"

Hope this helps.

Answer 2

First, place a number 1 in cell B2, then place the following formula in B3 and fill down:

=IF(A3=A2,B1,B1+1)

This assumes that the strings in your "emails" column are already sorted (i.e.: the duplicates are all next to one another).

Answer 3

You've edited your question to take the numbers out of the text in A, so MID and RIGHT don't work anymore, the function you're probably going to need is LOOKUP(). Basically you need to set up another table with just the unique values of A and what values they should map to.

Answer 4

Assuming that the repeated values are always adjacent (there is no "foo, bar, foo"), you can use this:

  A B
1 X 0
2 a 1
3 a 1
4 b 2
5 b 2
6 c 3
7 d 4
8 d 4
9 d 4

B2 is "=IF(A2=A1,B1,B1+1)", then filled down to B9

Answer 5

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.

Answer 6

Are you trying to auto-assign a number to each text column item, and have that number referenced in another column upon each repetition? For example, given the following text items, would you want output like this:

foo 1 bar 2 bar 2 foo 1 stuff 3 stuff 3

If so, there is an easy non-vba solution:

Put a value of 1 in cell b2. Put the formula =IFERROR(VLOOKUP(A3,A$2:B2,2,FALSE),MAX(B$2:B2)+1) in cell b3. Copy the formula from cell b3 down to the bottom of the data list.

For this solution, I am assuming that column headers are in row 1.