+ Reply to Thread
Results 1 to 10 of 10

import long list in custom sort edit

  1. 08-08-2015, 01:22 PM #1
    RAYSTORRY
    RAYSTORRY is offline
    Registered User
    Join Date
    08-08-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    4

    import long list in custom sort edit

    I need to an array of data by the sort order shown below. When I import this list into custom sort list edit function it only accepts charaters up to 42. How can I get the whole list to import?

    1
    11
    111
    1115
    1116
    112
    1121
    1122
    1123
    113
    1131
    1132
    1133
    1134
    1135
    1136
    1139
    115
    1150
    116
    1161
    1162
    117
    1171
    1172
    1173
    118
    1181
    1184
    119
    1190
    12
    121
    1211
    1213
    122
    1221
    1223
    1224
    1225
    1226
    124
    1241
    1242
    125
    1251
    1252
    1253
    1254
    1255
    1259
    2
    21
    211
    2111
    2112
    2113
    2114
    2119
    212
    2121
    2122
    2123
    2124
    2126
    2127
    2129
    213
    2133
    2134
    2135
    2136
    2137
    2139
    214
    2141
    2142
    215
    2150
    22
    221
    2211
    2212
    2213
    2214
    2215
    2216
    2217
    2218
    2219
    222
    2221
    2222
    2223
    2229
    223
    2231
    2232
    23
    231
    2311
    2312
    2314
    2315
    2316
    2317
    2318
    2319
    24
    241
    2412
    2413
    2419
    242
    2421
    2423
    2424
    2425
    2426
    2429
    243
    2431
    2432
    2433
    2434
    2435
    2436
    244
    2442
    2443
    2444
    2449
    245
    2451
    2452
    246
    2461
    2462
    2463
    247
    2471
    2472
    2473
    3
    31
    311
    3111
    3112
    3113
    3114
    3115
    3116
    3119
    312
    3121
    3122
    313
    3131
    3132
    32
    321
    3213
    3216
    3217
    3218
    3219
    323
    3231
    3233
    3234
    3235
    3239
    33
    331
    3311
    3312
    3313
    3314
    3315
    3319
    34
    341
    3411
    3412
    3413
    3414
    3415
    3416
    3417
    342
    3421
    3422
    344
    3441
    3442
    3443
    35
    351
    3511
    3512
    3513
    352
    3520
    353
    3531
    3532
    3533
    3534
    3535
    3536
    3537
    3538
    3539
    354
    3541
    3542
    3543
    3544
    3545
    3546
    355
    3550
    356
    3561
    3562
    3563
    3564
    3565
    3567
    4
    41
    411
    4112
    4113
    4114
    412
    4121
    4122
    4123
    4124
    4129
    413
    4131
    4132
    4133
    4134
    4135
    4138
    415
    4151
    4159
    416
    4161
    4162
    42
    421
    4211
    4212
    4213
    4214
    4215
    4216
    4217
    5
    51
    511
    5111
    5112
    5113
    5114
    5119
    52
    521
    5211
    5212
    5213
    5214
    5215
    5216
    522
    5221
    5222
    5223
    5224
    5225
    523
    5231
    5232
    5234
    5235
    5236
    5237
    524
    5241
    5242
    5244
    5245
    5249
    525
    5250
    53
    531
    5311
    5312
    5313
    5314
    5315
    5316
    5319
    532
    5321
    5322
    5323
    533
    5330
    54
    541
    5411
    5412
    5413
    5414
    5419
    542
    5421
    5422
    5423
    543
    5431
    5432
    5433
    5434
    5435
    5436
    544
    5441
    5442
    5443
    5449
    6
    61
    612
    6121
    6122
    6123
    6125
    6126
    613
    6131
    6132
    6139
    614
    6141
    6142
    6143
    6144
    6145
    6146
    6147
    6148
    62
    621
    6211
    6212
    6214
    6215
    6219
    622
    6221
    6222
    623
    6231
    6232
    624
    6240
    7
    71
    711
    7111
    7112
    7113
    7114
    7115
    712
    7121
    7122
    7123
    7124
    7125
    7129
    713
    7130
    72
    721
    7211
    7213
    7214
    7215
    7219
    722
    7220
    8
    81
    811
    8111
    8112
    8113
    8114
    8115
    8116
    8117
    8118
    8119
    812
    8121
    8122
    8123
    8124
    8125
    8126
    8127
    8129
    813
    8131
    8132
    8133
    8134
    8135
    8137
    8139
    814
    8141
    8142
    8143
    8149
    82
    821
    8211
    8212
    8213
    8214
    8215
    822
    8221
    8222
    8223
    8229
    823
    8231
    8232
    8233
    8234
    8239
    9
    91
    911
    9111
    9112
    9119
    912
    9120
    913
    9132
    9134
    9139
    92
    921
    9211
    9219
    923
    9231
    9232
    9233
    9234
    9235
    9236
    9239
    924
    9241
    9242
    9244
    9249
    925
    9251
    9259
    926
    9260
    927
    9271
    9272
    9273
    9274
    9275
    9279
    Reply With QuoteRegister To Reply
  2. 08-11-2015, 07:26 AM #2
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: import long list in custom sort edit

    You just need to sort your list as texts not as nuneric values
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  3. 08-12-2015, 07:25 AM #3
    RAYSTORRY
    RAYSTORRY is offline
    Registered User
    Join Date
    08-08-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    4

    Re: import long list in custom sort edit

    I have tried that and it doesn't work
    Reply With QuoteRegister To Reply
  4. 08-12-2015, 10:26 AM #4
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: import long list in custom sort edit

    You probably just changed format of cells to text - it's not enough. The values have to be real texts. see attached file just push sort A->Z ore Z-A and then see in sort dialog how it is sorted (on column B) and see formula in B - it really makes text out of a number.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 08-15-2015, 05:36 PM #5
    RAYSTORRY
    RAYSTORRY is offline
    Registered User
    Join Date
    08-08-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    4

    Re: import long list in custom sort edit

    You are right I have not explained my self very well. The list I posted is the order by which I want to sport another array of data. This array is currently sorted like this:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    11
    12
    21
    22
    23
    24
    31
    32
    33
    34
    35
    41
    42
    51
    52
    53
    54
    61
    62
    71
    72
    81
    82
    91
    92
    111
    112
    113
    115
    116
    117
    118
    119
    121
    122
    124
    125
    211
    212
    213
    214
    215
    221
    222
    223
    231
    241
    242
    243
    244
    245
    246
    247
    311
    312
    313
    321
    323
    331
    341
    342
    344
    351
    352
    353
    354
    355
    356
    411
    412
    413
    415
    416
    421
    511
    521
    522
    523
    524
    525
    531
    532
    533
    541
    542
    543
    544
    612
    613
    614
    621
    622
    623
    624
    711
    712
    713
    721
    722
    811
    812
    813
    814
    821
    822
    823
    911
    912
    913
    921
    923
    924
    925
    926
    927
    1115
    1116
    1121
    1122
    1123
    1131
    1132
    1133
    1134
    1135
    1136
    1139
    1150
    1161
    1162
    1171
    1172
    1173
    1181
    1184
    But I want to get to:
    121
    12
    1190
    119
    1184
    1181
    118
    1173
    1172
    1171
    117
    1162
    1161
    116
    1150
    115
    1139
    1136
    1135

    I am not trying to sort that latter but the former. I am trying to write VB macro that uses a custom sort to do it but I am not experienced in using VB.
    Reply With QuoteRegister To Reply
  6. 08-15-2015, 05:42 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: import long list in custom sort edit

    RAYSTORRY, welcome to the forum

    Instead of posting a string of values/text/whatever, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  7. 08-15-2015, 07:56 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: import long list in custom sort edit

    The closest that I can get without a macro is to use in column B the following formula and then sort on column B
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    RESULT:
    A
    B
    39
    12
    		 12
    40
    121
    		 12
    41
    122
    		 12
    42
    124
    		 12
    43
    125
    		 12
    44
    21
    		 21
    45
    211
    		 21
    46
    212
    		 21
    47
    213
    		 21
    48
    214
    		 21
    49
    215
    		 21
    50
    22
    		 22
    51
    221
    		 22
    52
    222
    		 22
    53
    223
    		 22
    54
    23
    		 23
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  8. 08-17-2015, 10:12 AM #8
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: import long list in custom sort edit

    Hi RAYSTORRY, you have probably not tried my file (besides, you've changed order from ascending to descending one). look at the macro (recorded) working on your data. it sorts columns A and B based on texts in column B.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-14-2015, 04:37 AM #9
    RAYSTORRY
    RAYSTORRY is offline
    Registered User
    Join Date
    08-08-2015
    Location
    london
    MS-Off Ver
    2013
    Posts
    4

    Re: import long list in custom sort edit

    Many thanks Kaper. Yes I see how it is working, however, I can't get it to work in my actual dataset. I have attached for information. Can you help me in the next step, We are so close.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 09-14-2015, 05:32 AM #10
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: import long list in custom sort edit

    I don't see the point. Just select all your data with single-row header (A2:O496), open sort dialog, make sure headers are checked, select Code Text as sorting key and sort ascending.
    If excel asks you if "what looks like number" should be sorted as number or as mixed contents (text and numbers) select text and numbers, not everything as numbers (you are trying to avoid this).

    Coded in macro it could be: 
    Please Login or Register  to view this content.
    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. Sort a long list of string
    By calvinfoo in forum Excel General
    Replies: 3
    Last Post: 05-12-2015, 02:11 PM
  2. [SOLVED] Splitting long list into smaller lists while preserving sort order
    By davidm_uk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-03-2014, 02:39 PM
  3. long list won't sort
    By bwiebe in forum Excel General
    Replies: 6
    Last Post: 07-23-2013, 10:19 AM
  4. Sort list of GPS waypoints by both Lat and Long, not by Lat then Long.
    By CarolinaJim in forum Excel General
    Replies: 4
    Last Post: 09-22-2012, 04:21 PM
  5. [SOLVED] Excel sort by Fill Color by custom list sort
    By Dash4Cash in forum Excel General
    Replies: 2
    Last Post: 07-29-2005, 06:05 PM

Tags for this Thread

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