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

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.

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).

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 be 2 5 be 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.

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.

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.

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.