Assign Code to Numbers

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
201
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

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

<colgroup><col><col></colgroup><tbody>
</tbody>
Thank you in advance - It would be greatly helpful
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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?
 
Upvote 0
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

0111
0111
0111
0111
0111
0111
0111
0131
0131
0131
0131
0131
0131
0131
0131
0133
0133
0133
0133

<tbody>
</tbody>
 
Last edited:
Upvote 0
I have tested quite a few values, not all, but I think this works
Code:
=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)
 
Upvote 0
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
 
Upvote 0
NO, it is an Excel formula. Why use VBA when a formula does it, just plug it ito the cells adjacent to the codes.
 
Upvote 0
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).
Code:
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top