Results 1 to 8 of 8

Thread: Assign Code to Numbers
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    187
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Assign Code to Numbers

    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 529 A
    600-1090 B
    1111-1220 C
    1311- 2599 D
    2630-3299 E
    3311-3800 F
    3911-4320 G
    4400-4530 H
    4610-5029 I
    5101-5309 J
    5411-6020 K
    6210-6420 L
    6611-6639 M
    6711-6720 N
    6910-6999 O
    7000-7320 P
    7501-7720 Q
    8010-8220 R
    8401-8599 S
    8601-8790 T
    8910-9003 U
    9111-9112 V
    9113-9999 W
    Thank you in advance - It would be greatly helpful

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,312
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Assign Code to Numbers

    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    187
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign Code to Numbers

    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
    Last edited by Blessy Clara; May 16th, 2019 at 09:29 AM.

  4. #4
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign Code to Numbers

    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)

  5. #5
    Board Regular
    Join Date
    Mar 2010
    Posts
    187
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign Code to Numbers

    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

  6. #6
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign Code to Numbers

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

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,312
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Assign Code to Numbers

    Quote Originally Posted by Blessy Clara View Post
    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 by Rick Rothstein; May 18th, 2019 at 11:37 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    546
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign Code to Numbers

    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...nge-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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •