Assign Code to Numbers

B

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
204
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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

[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]
 
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
Blessy Clara said:
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
Click to expand...

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
You must log in or register to reply here.

Forum statistics

Threads
1,224,884
Messages
6,181,573
Members
453,054
Latest member
arz007

Share this page

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