Hello,I would like to change an address list of"surnames, names"(strings) ---> to numbers: Lehmann, Arthur ---> changed to ---> 1Lehmann, Arthur ---> changed to --->1Lehmann, Arthur ---> changed to --->1Meissner, Ariane ---> changed to --->2Meissner, Ariane ---> changed to --->2Meissner, Ariane ---> changed to --->2Meyer, Christian ---> changed to --->3Meyer, Christian ---> changed to --->3Meyer, Christian ---> changed to --->3etc. ....How is this possible?Many thanks in advance,Niklas K.

Hi, You could use something like the below code. Call it like StringsToNumbers(Range("A2:A10"), Range("B2:B10")) Sub StringsToNumbers( _ ByVal StringRange As Range, _ ByVal OutputRange As Range, _ Optional ByVal StartingNumber As Long = 1 _ ) Dim Names As New Collection Dim Index As Long Dim Row As Long Dim Column As Long If StringRange.Cells.Count <> OutputRange.Cells.Count Or StringRange.Areas.Count <> OutputRange.Areas.Count Then Exit Sub Index = 1 For Row = 1 To StringRange.Rows.Count For Column = 1 To StringRange.Columns.Count If Not InCollection(Names, StringRange(Row, Column).Value2) Then Names.Add Index, StringRange(Row, Column).Value2 OutputRange(Row, Column).Value2 = Index Index = Index + 1 Else OutputRange(Row, Column) = Names.Item(CStr(StringRange(Row, Column))) End If Next Column Next Row End Sub

Niklas Kalz

Copper Contributor

Oct 08, 2017

Solved

String-Variables should be changed to numbers

Hello,

I would like to change an address list of

"surnames, names"(strings) ---> to numbers:

Lehmann, Arthur ---> changed to ---> 1

Lehmann, Arthur ---> changed to --->1

Meissner, Ariane ---> changed to --->2

Meyer, Christian ---> changed to --->3

etc. ....

How is this possible?

Many thanks in advance,

Niklas K.

Hi,

You could use something like the below code. Call it like StringsToNumbers(Range("A2:A10"), Range("B2:B10"))

Sub StringsToNumbers( _
    ByVal StringRange As Range, _
    ByVal OutputRange As Range, _
    Optional ByVal StartingNumber As Long = 1 _
    )

    Dim Names As New Collection
    Dim Index As Long
    Dim Row As Long
    Dim Column As Long

    If StringRange.Cells.Count <> OutputRange.Cells.Count Or StringRange.Areas.Count <> OutputRange.Areas.Count Then Exit Sub
    Index = 1

    For Row = 1 To StringRange.Rows.Count
        For Column = 1 To StringRange.Columns.Count
            If Not InCollection(Names, StringRange(Row, Column).Value2) Then
                Names.Add Index, StringRange(Row, Column).Value2
                OutputRange(Row, Column).Value2 = Index
                Index = Index + 1
            Else
                OutputRange(Row, Column) = Names.Item(CStr(StringRange(Row, Column)))
            End If
        Next Column
    Next Row

End Sub

6 Replies

SergeiBaklan
Diamond Contributor
Oct 09, 2017
Alternatively, if list is sorted (doens't matter how) - add 1 in B1 and formula as on screenshot in B2, drag down till end of the list
- Niklas Kalz
  Copper Contributor
  Oct 10, 2017
  Many thanks for your good idea and your support!!
- Zack Barresse
  Iron Contributor
  Oct 09, 2017
  That requires the name column to be sorted (constantly). I thought of posting a formula, but the OP asked for VBA. A formula which doesn't need the data to be sorted could be something like the below formula, assuming data starts in row 2, names in column A, headers in row 1, and count/number formula starting in B2 and copied down.
  
  =IF(COUNTIF($A$2:A2,A2)=1,SUM(B1)+1,INDEX($B$1:B1,MATCH(A2,$A$2:A2,0)+1))
  - Niklas Kalz
    Copper Contributor
    Oct 10, 2017
    Another good approach and solution, many thanks! Niklas

Zack Barresse

Iron Contributor

Oct 09, 2017

Hi,

You could use something like the below code. Call it like StringsToNumbers(Range("A2:A10"), Range("B2:B10"))

Sub StringsToNumbers( _
    ByVal StringRange As Range, _
    ByVal OutputRange As Range, _
    Optional ByVal StartingNumber As Long = 1 _
    )

    Dim Names As New Collection
    Dim Index As Long
    Dim Row As Long
    Dim Column As Long

    If StringRange.Cells.Count <> OutputRange.Cells.Count Or StringRange.Areas.Count <> OutputRange.Areas.Count Then Exit Sub
    Index = 1

    For Row = 1 To StringRange.Rows.Count
        For Column = 1 To StringRange.Columns.Count
            If Not InCollection(Names, StringRange(Row, Column).Value2) Then
                Names.Add Index, StringRange(Row, Column).Value2
                OutputRange(Row, Column).Value2 = Index
                Index = Index + 1
            Else
                OutputRange(Row, Column) = Names.Item(CStr(StringRange(Row, Column)))
            End If
        Next Column
    Next Row

End Sub

Niklas Kalz
Copper Contributor
Oct 10, 2017
Many thanks for your detailed answer which is really useful!!
Niklas

Forum Discussion

String-Variables should be changed to numbers