assigning numbers to companies
 

I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company "care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

assigning numbers to companies
 

maybe something like this:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"jamesea" wrote:

I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company "care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

assigning numbers to companies
 

ignore my answer.. don't know what I was thinking!


--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

maybe something like this:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"jamesea" wrote:

I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company "care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

assigning numbers to companies
 

OK, something like this will work:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) <=
"GR" Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

ignore my answer.. don't know what I was thinking!


--

Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

maybe something like this:

Function GetCompanyID(strCompany As String) As String
If Left(UCase(strCompany), 1) = "A" Or Left(UCase(strCompany), 2) = "GR"
Then
GetCompanyID = "01"
End If
End Function



--

Hope that helps.

Vergel Adriano


"jamesea" wrote:

I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company "care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

assigning numbers to companies
 

Below code assumes your sheet name is "sheet1",
column A is allocated for ID numbers, column B holds your names
and columns AA to AC are available
You can run this where ever you like.

rgds

Sub makeID()

Dim rng As Range
Dim i As Range
Dim rng1 As Range
Dim str1 As String
Dim rng2 As Range
Dim str2 As String
Dim rng3 As Range
Dim str3 As String

Application.ScreenUpdating = False

Set rng1 = Application.Intersect(Worksheets("sheet1") _
.Range("aa2:aa65526"), Worksheets("sheet1").UsedRange.EntireRow)
str1 = "=Substitute(B2,"" "","""")"
Set rng2 = Application.Intersect(Worksheets("sheet1") _
.Range("ab2:ab65526"), Worksheets("sheet1").UsedRange.EntireRow)
str2 = "=MID(aa2,1,4)"
Set rng3 = Application.Intersect(Worksheets("sheet1") _
.Range("ac2:ac65526"), Worksheets("sheet1").UsedRange.EntireRow)
str3 = "=Codeit(ab2)"

rng1.Formula = str1
rng2.Formula = str2

With Worksheets("sheet1")
Set rng = .Range("ab2", .Range("ab" & Rows.Count). _
End(xlUp))
End With

For Each i In rng
Select Case Len(i.Value)
Case 1: i.Value = i.Value & "aaa"
Case 2: i.Value = i.Value & "aa"
Case 3: i.Value = i.Value & "a"
End Select
Next i

rng3.Formula = str3

With Worksheets("sheet1")
Set rng = .Range("ac2", .Range("ac" & Rows.Count). _
End(xlUp))
End With

For Each i In rng ' below numbers are your hardcoded sections
Select Case i.Value
Case 65656565 To 71829090
Worksheets("sheet1").Range("A" & i.Row) = 1
Case 71836565 To 83659090
Worksheets("sheet1").Range("A" & i.Row) = 2
Case 83666565 To 84726967
Worksheets("sheet1").Range("A" & i.Row) = 3
Case 84726968 To 90909090
Worksheets("sheet1").Range("A" & i.Row) = 4
End Select
Next i

Worksheets("sheet1").Range("aa1.ac1").EntireColumn . _
Delete

Application.ScreenUpdating = True

End Sub


Pls put below function on a standart module ( Function originally
coded by Frank Isaac )

Function CodeIt(rngName As Excel.Range) As String
Dim iX As Integer, iVal As Integer
For iX = 1 To Len(rngName)
iVal = Asc(UCase(Mid(rngName, iX, 1))) ' - 64
If iVal < 65 Or iVal 90 Then
CodeIt = CodeIt & "0"
Else
CodeIt = CodeIt & CStr(iVal)
End If
Next
End Function

assigning numbers to companies
 

For a VBA solution, maybe this. Adjust depending on how/where your company
ID data is stored. This assumes the structure as you have shown:

Public Function GetCompanyID(WhichCompany As String) As String
Dim CompIDData As Variant
Dim IDNum As Long

CompIDData = Range("rngCompIDs").Value

For IDNum = LBound(CompIDData, 1) To UBound(CompIDData, 1)
If (UCase(Left(WhichCompany, Len(CompIDData(IDNum, 2)))) =
CompIDData(IDNum, 2)) And (UCase(Left(WhichCompany, Len(CompIDData(IDNum,
3)))) <= CompIDData(IDNum, 3)) Then
GetCompanyID = CompIDData(IDNum, 1)
Exit Function
End If
Next

GetCompanyID = "#N/A"

End Function

However, you could use worksheets functions instead

NickHK

"jamesea" wrote in message
...
I need to assign companies to one of four different numbers depending on
where they fall in the alphabet, for example I need to assign a company
"care
homes ltd" as 01. Any ideas on the formula?


01 A GR
02 GS SA
03 SB THEC
04 THED Z

assigning numbers to companies
 

Thank you very much Vergel & Nick. You helped me as well.

So it is only "=GetID(B2)" on A column.

Kind regards


Public Function GetID(Company As String) As String
Select Case UCase(Company)
Case "A" To "GRZZZZZZZZZZZZZZZZZ": GetID = 1
Case "GS" To "SAZZZZZZZZZZZZZZZZ": GetID = 2
Case "SB" To "THECZZZZZZZZZZZZZZ": GetID = 3
Case "THED" To "ZZZZZZZZZZZZZZZZ": GetID = 4
End Select
End Function