Assign Code to Numbers

[Blessy Clara]

I have an excel Sheet with Column A containing the ANSZIC Code - Ranging from 0 to 9999
There are millions of records and i wanna a assign a code to classify these Numbers as Follows Ex

For Numbers FROM 0-529 it must be assigned A, and like wise

[TABLE="width: 401"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]0 – 529[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]600-1090[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1111-1220[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1311- 2599[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2630-3299[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]3311-3800[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]3911-4320[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]4400-4530[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]4610-5029[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]5101-5309[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5411-6020[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]6210-6420[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]6611-6639[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]6711-6720[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6910-6999[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]7000-7320[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]7501-7720[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]8010-8220[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]8401-8599[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]8601-8790[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]8910-9003[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]9111-9112[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]9113-9999[/TD]
[TD]W[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance - It would be greatly helpful

 

[Rick Rothstein]

You have gaps between the ranges for each letter... what should happen if a number should fall inside a gap? For example, what should be done if the number is 1100 or 5100?

 

[Blessy Clara]

Hi Rick

Thank you for your response - Yes there are gaps (few Numbers/ranges do not required to be categorized) - So it is okay if those numbers/ranges are ignored/left blank - Given below is an example of how the data occurs in Col

Example - Numbers from 530 to 599 (I don't require those records) So - to be ignored/left Blank

[TABLE="width: 116"]
<tbody>[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0111[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0131[/TD]
[/TR]
[TR]
[TD]0133[/TD]
[/TR]
[TR]
[TD]0133[/TD]
[/TR]
[TR]
[TD]0133[/TD]
[/TR]
[TR]
[TD]0133[/TD]
[/TR]
</tbody>[/TABLE]

 

[theBardd]

I have tested quite a few values, not all, but I think this works

=CHAR(SUMPRODUCT(--(A1>={0;530;600;1091;1111;1221;1311;2600;2630;3300;3311;3801;3911;4321;4400;4531;4610;5030;5101;5310;5411;6021;6210;6421;6611;6640;6711;6721;6910;7000;7000;7321;7501;7721;8010;8221;8401;8600;8601;8791;8910;9004;9111;9113;9113;10000}),
                                    {1;-33;34;-34;35;-35;36;-36;37;-37;38;-38;39;-39;40;-40;41;-41;42;-42;43;-43;44;-44;45;-45;46;-46;47;-47;48;-48;49;-49;50;-50;51;-51;52;-52;53;-53;54;-54;55;-55})+64)

 

[Blessy Clara]

Hi Bardd, Thank you for your response - I am not quite sure on how and where to use this - this doesn't look as a VBA

 

[theBardd]

NO, it is an Excel formula. Why use VBA when a formula does it, just plug it ito the cells adjacent to the codes.

 

[Rick Rothstein]

Hi Bardd, Thank you for your response - I am not quite sure on how and where to use this - this doesn't look as a VBA

If you want a VBA solution, here is a function that can be called from within other VBA code or placed within an Excel formula depending on your needs. The function takes one argument, the code number, and returns the letter you indicated you wanted for that number. If a code number is passed in that does not fall within any of your ranges, the function returns the empty text string (""). The code is simple enough that you could do away with the function housing and embed the body of the code directly within your own code if need be (just change the Code variable name in the Select Case Code code line to whatever variable name you used to hold the code number).

Function ANSZIC(Code As Long) As String
  Select Case Code
    Case 0 To 529: ANSZIC = "A"
    Case 600 To 1090: ANSZIC = "B"
    Case 1111 To 1220: ANSZIC = "C"
    Case 1311 To 2599: ANSZIC = "D"
    Case 2630 To 3299: ANSZIC = "E"
    Case 3311 To 3800: ANSZIC = "F"
    Case 3911 To 4320: ANSZIC = "G"
    Case 4400 To 4530: ANSZIC = "H"
    Case 4610 To 5029: ANSZIC = "I"
    Case 5101 To 5309: ANSZIC = "J"
    Case 5411 To 6020: ANSZIC = "K"
    Case 6210 To 6420: ANSZIC = "L"
    Case 6611 To 6639: ANSZIC = "M"
    Case 6711 To 6720: ANSZIC = "N"
    Case 6910 To 6999: ANSZIC = "O"
    Case 7000 To 7320: ANSZIC = "P"
    Case 7501 To 7720: ANSZIC = "Q"
    Case 8010 To 8220: ANSZIC = "R"
    Case 8401 To 8599: ANSZIC = "S"
    Case 8601 To 8790: ANSZIC = "T"
    Case 8910 To 9003: ANSZIC = "U"
    Case 9111 To 9112: ANSZIC = "V"
    Case 9113 To 9999: ANSZIC = "W"
  End Select
End Function

 

[Dr. Demento]

If you have "millions of records," may I recommend using an array instead of either a UDF (Rick's suggestion) or theBardd's formula; both of these will take an inordinate amount of time and no guarantee it won't crash. In fact, it would probably be easiest if you sub-divided the entire lists into smaller chunks regardless of which path you choose.

That being said, below is code that would assign each number an ANSZIC value. Please copy a subset of data to a new sheet and run the sub to see how it will output. Please note, if there are any blank rows or columns in the data, this will only capture up to that empty row/column (due to UsedRange to define array; there are other functions that work to capture an entire range, regardless of empty columns/row. See:
http://strugglingtoexcel.com/2014/05/26/actual-used-range-excel-vba/)

Hope this helps.

Sub test_ANSZIC()Dim sht As Worksheet
  Set sht = ActiveSheet
  
Dim arr As Variant
  arr = sht.UsedRange.value
  ReDim arr(LBound(arr, 1) To UBound(arr, 1), _
            LBound(arr, 2) To UBound(arr, 2) + 1)
Dim i As Long
  For i = LBound(arr, 1) To UBound(arr, 1)
    arr(i, 2) = ANSZIC(arr(i, 1))
  Next i
  
  With ActiveWorkbook.Worksheets.Add
    .Range(.Cells(1, 1), .Cells(UBound(arr, 1), UBound(arr, 2))) = arr
  End With
  
End Sub