+ Reply to Thread
Results 1 to 7 of 7

Custom Sort Exceeds Max Character Limit.

  1. 10-13-2016, 03:45 PM #1
    kohota
    kohota is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Eagle, Colorado
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Custom Sort Exceeds Max Character Limit.

    I am currently working on a spreadsheet to organize the doors and lock cores at my place of work. I want to be able to create a custom sort and then add this sort to the workbook, so that other employees who can access the workbook, are able to use the same sort. I can go through the settings and create a custom list that exceeds the limit by going to options, advanced, and then editing my custom list. However, if I then click add a message pops up that says, "The maximum length for a custom list has been exceeded. Only the first 255 characters will be saved." How can I get around this and what suggestions do you have?

    I am fairly new to excel so I will struggle with advanced concepts such as macros and VBA.

    A sample of my data is

    A1a
    A1
    A1e
    A2
    A2e
    A3
    A3e
    A4
    A4e
    A5
    A5e
    A5a
    A6
    A7
    A8
    A9
    A10
    A11
    A12
    A13
    A14
    A15
    A16
    A17
    A18
    A19

    And so on. It transitions through letters as well.

    Thank you for whatever input you can provide.
    Reply With QuoteRegister To Reply
  2. 10-13-2016, 04:11 PM #2
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Custom Sort Exceeds Max Character Limit.

    Welcome to the board.

    I can provide a user-defined function that will return a sortable result for those values if VBA is acceptable.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  3. 10-14-2016, 11:16 AM #3
    kohota
    kohota is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Eagle, Colorado
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Custom Sort Exceeds Max Character Limit.

    I will copy and paste the complete list if that will be helpful. I will need to be given a brief summary of how I will utilize the function. Also, is there any way that this function will stay with the worksheet so that a coworker could utilize it on another computer?

    A1a
    A1
    A1e
    A2
    A2e
    A3
    A3e
    A4
    A4e
    A5
    A5e
    A5a
    A6
    A7
    A8
    A9
    A10
    A11
    A12
    A13
    A14
    A15
    A16
    A17
    A18
    A19
    A20
    A21
    A22
    A23
    A24
    A25
    A26
    A27
    A28
    A29
    A30
    A31
    B1
    B2
    B3
    B4
    B5
    B6
    B7 A&B
    B8 A&B
    B9 A&B
    B10
    B11
    B12
    B13
    B14
    B15
    B16
    B17
    B18
    B19
    B20
    B21
    B22
    B23
    B24
    B25
    B26
    B27
    B28
    B29
    B30
    B31
    B32
    B33
    B34
    B35
    B36
    B37
    B38
    B39
    B40
    B41
    B42
    B43
    B44
    B45
    B46
    B47
    B48
    B49
    B50
    B51
    B52
    B53
    B54
    B55
    B56
    B57
    B58
    B59
    B60
    B61
    B62
    B63
    B64
    B65
    B66
    B67
    B68
    B69
    B70
    B71
    B72
    B73
    B74
    B75
    B76
    B77
    B78
    B79
    B80
    B81
    B82
    B83
    B84
    B85
    B86
    B87
    B88
    B89
    B90
    B91
    B92
    B93
    B94
    B95
    B96
    B97
    B98
    B99
    C1
    C2
    C3
    C4
    C5
    C6
    C7
    C8
    C9
    C10
    C11
    C12
    C13
    C14
    C15
    C16
    C17
    D1
    D2
    D3
    D4
    D5
    D6
    D7
    D8
    D9
    D10
    E1
    E2
    E3
    E4
    E5
    E6
    E7
    E8
    E9
    E10
    E11
    E12
    E13
    E14
    E15
    E16
    E17
    E18
    E19
    E20
    E21
    E22
    E23
    E24
    E25
    E26
    E27
    E28
    E29
    E30
    E31
    E32
    E33
    E34
    E35
    E36
    E37
    E38
    E39
    E40
    E41
    E42
    E43
    F1
    F2
    F3
    G1
    G2
    G3
    G4
    G5
    G6
    G7
    G8
    G9
    G10
    G11
    G12
    G13
    G14
    G15
    G16
    G17
    G18
    G19
    G20
    G21
    G22
    G23
    G24
    G25
    G26
    G27
    G28a
    G28b
    L1
    L2
    L3
    S1
    S2
    T1
    T2
    W1
    Reply With QuoteRegister To Reply
  4. 10-14-2016, 03:59 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Custom Sort Exceeds Max Character Limit.

    A
    B
    C
    1
    Input
    Sort
    2
    		 A1a A01a B2: =PadNum(A2, 2)
    3
    		 A1 A01
    4
    		 A1e A01e
    5
    		 A2 A02
    6
    		 A2e A02e
    7
    		 A3 A03
    8
    		 A3e A03e
    9
    		 A4 A04
    10
    		 A4e A04e
    11
    		 A5 A05
    12
    		 A5e A05e
    13
    		 A5a A05a
    14
    		 A6 A06
    15
    		 A7 A07
    16
    		 A8 A08
    17
    		 A9 A09
    18
    		 A10 A10
    19
    		 A30 A30
    20
    		 A31 A31
    21
    		 B1 B01
    22
    		 B2 B02
    23
    		 B3 B03
    24
    		 B4 B04
    25
    		 B5 B05
    26
    		 B6 B06
    27
    		 B7 A&B B07 A&B
    28
    		 B8 A&B B08 A&B
    29
    		 B9 A&B B09 A&B
    30
    		 B10 B10
    31
    		 B99 B99
    32
    		 C1 C01
    33
    		 C9 C09
    34
    		 C10 C10
    35
    		 C17 C17
    36
    		 D1 D01
    37
    		 D9 D09
    38
    		 D10 D10
    39
    		 E1 E01
    40
    		 E9 E09
    41
    		 E10 E10
    42
    		 E43 E43
    43
    		 F1 F01
    44
    		 F3 F03
    45
    		 G1 G01
    46
    		 G9 G09
    47
    		 G10 G10
    48
    		 G27 G27
    49
    		 G28a G28a
    50
    		 G28b G28b
    51
    		 L1 L01
    52
    		 L2 L02
    53
    		 L3 L03
    54
    		 S1 S01
    55
    		 S2 S02
    56
    		 T1 T01
    57
    		 T2 T02
    58
    		 W1 W01


    Here's the UDF:

    Please Login or Register  to view this content.
    ... this function will stay with the worksheet so that a coworker could utilize it on another computer?
    Sure -- save as an xlsm file, and other users (and you) will need to enable macros.
    Reply With QuoteRegister To Reply
  5. 10-14-2016, 04:24 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Custom Sort Exceeds Max Character Limit.

    I should add that if that is the complete list, you can do this without VBA. Just add a column to your table that looks up the sort order on the entry.
    Reply With QuoteRegister To Reply
  6. 10-17-2016, 11:22 AM #6
    kohota
    kohota is offline
    Registered User
    Join Date
    10-13-2016
    Location
    Eagle, Colorado
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Custom Sort Exceeds Max Character Limit.

    Thank you for your help so far.

    I apologize for my ignorance, but can you possibly step me through what you mean when you say to add a column that looks up the sort order.
    Reply With QuoteRegister To Reply
  7. 10-17-2016, 02:13 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Custom Sort Exceeds Max Character Limit.

    A
    B
    C
    D
    E
    1
    Input
    Sort
    Tbl
    2
    		 A20
    27
    		 B2: =MATCH(A2, $E$2:$E$248, 0) A1a
    3
    		 A10
    17
    		 A1
    4
    		 E12
    176
    		 A1e
    5
    		 B32
    70
    		 A2
    6
    		 B72
    110
    		 A2e
    7
    		 C6
    143
    		 A3
    8
    		 D5
    159
    		 A3e
    9
    		 B85
    123
    		 A4
    10
    		 B86
    124
    		 A4e
    11
    		 E1
    165
    		 A5
    12
    		 C12
    149
    		 A5e
    13
    		 B44
    82
    		 A5a
    14
    		 C9
    146
    		 A6
    15
    		 B80
    118
    		 A7
    16
    		 E25
    189
    		 A8
    17
    		 B11
    49
    		 A9
    18
    		 A1a
    1
    		 A10
    19
    		 B77
    115
    		 A11
    20
    		 L2
    241
    		 A12
    21
    		 B18
    56
    		 A13
    22
    		 B26
    64
    		 A14
    23
    		 B83
    121
    		 A15
    24
    		 B89
    127
    		 A16
    25
    		 G1
    211
    		 A17
    26
    		 B21
    59
    		 A18
    27
    		 B56
    94
    		 A19
    28
    		 G26
    236
    		 A20
    29
    		 B36
    74
    		 A21
    30
    		 B57
    95
    		 A22


    Col E is your input values in preferred sort order (shown here in part).

    Sort cols A & B by col B.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Modify Custom Sort List Character Limit
    By Michael1776 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-23-2022, 02:55 AM
  2. Macro to sort by custom list >256 character limit
    By KoreanIan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-16-2015, 01:13 PM
  3. [SOLVED] Modify Custom Sort List Character Limit... VBA...
    By Evilbober in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-25-2014, 01:13 PM
  4. When a Value exceeds limit
    By mills49 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-21-2013, 11:16 PM
  5. If Statement Exceeds 1024 Character Limit
    By bigman8424 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2013, 11:27 AM
  6. Notification when a value exceeds a limit
    By -mads in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2012, 07:43 PM
  7. [SOLVED] if the cell value exceeds the limit to have a dialog box appear
    By Dschro in forum Excel General
    Replies: 2
    Last Post: 12-15-2005, 06:35 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1