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

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.

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

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 4

Depending on what you mean by value -

If you mean "repeating group" then a macro like this may work -

Sub Check()
    Dim start As Integer
    start = 1
    For Row = 2 To 12
        Cells.Item(Row, 2) = start
        If Cells.Item(Row, 1) <> Cells.Item(Row + 1, 1) Then
            start = start + 1
        End If
    Next
End Sub

If you mean "take the number at the end of each value in column A" then this may work -

Sub Check()
    Dim i As Integer
    Dim value As String
    Dim number As String
    Dim c As String
    For Row = 2 To 12
        number = ""
        value = Cells.Item(Row, 1)
        For i = 1 To Len(value)
            c = Mid(value, i, 1)
            If IsNumeric(c) Then
                number = number & c
            End If
        Next
        Cells.Item(Row, 2) = number
    Next
End Sub

Edit - According to the OP's comment, I'm guessing they mean "repeating group" but I'll leave both code samples here.

Edit - Copy and paste the code into the excel Visual Basic Editor and click play. Replace the constants 2 and 12 with the values of your start and end rows.

Answer 5

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 6

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