excel - Sorting Different Length Strings in VBA So That Longest Aren't Last -


i'm trying sort list of room numbers using vba. however, of rooms have "a" or "b" @ end of number denote suite style living. want sort list of rooms ones or b aren't sorted end of list because they're longer strings. tried sort function in vba so

startrange.sort key1:=sortrange, order1:xlascending

where startrange , sortrange range objects defined elsewhere. function sorts data correctly, results such as

411 418 422 432 415a 415b 428b

whereas i'd them sorted as:

411 415a 415b 418 422 428b 432

i thought trying sort rooms text counting first 3 characters , sorting each individual block of , bs depending on last letter, further complicated 3 things:

  1. some of rooms 4 digits (ex. 1195, 1045, etc). none of these rooms have issues or b.
  2. some of rooms prefaced "w" or "e" depending on if in west or east half of building. of these rooms have or b suffix, sorted correctly in list way them sorted (ex. w134, w135a, w135b, w136, , on).
  3. not of rooms end in same sheet in excel. i'm working master list of rooms several different buildings, , each sheet sorted building.

the format of cells has been both "general" , "text" , both have had same result. can offered appreciated in solving issue. thank in advance!

this following adaptation of what's on page

sub sort_special(startrange range, sortrange range)     dim long     sortrange       = 0 9         .replace what:=i, replacement:="zzzzz" & i, lookat:=xlpart, matchcase:=false       next     end      startrange.sort key1:=sortrange, order1:=xlascending, header:=xlno      sortrange       = 0 9         .replace what:="zzzzz", replacement:="", lookat:=xlpart, matchcase:=false       next     end end sub

excel sorts numeric fields ahead of alphanumeric ones. replacing each digit dummy field in digit prefixed arbitrary string 'zzzzz' you're forcing excel treat fields alphanumeric. sort on these converted fields , afterwards remove each sequence of 'zzzzz'.

hopefully don't have room #s end 5 zs.


Comments

Post a Comment

Popular posts from this blog

Command prompt result in label. Python 2.7 -

i want put result(output) of shell command label. command works, label shows "0", however, in command prompt, result shown correctly, need showed in label. doing ubuntu. myg1 = button(root, text="rodyti informacija", command=lambda: gauti()) myg1.pack(side=bottom) def gauti(): imti = tekstas.get("1.0", "end-1c") info = subprocess.call("id '{imti}' ".format(imti=imti), shell=true) w = label(root, text= info) w.pack(side = bottom) subprocess.call() returns exit code of process created (in case 0 success). if want text output of process, should instead call subprocess.check_output() .
Read more

javascript - How do I use URL parameters to change link href on page? -

i change href value of link using url parameters. example: default <a id="dlink" href="link-1">link</a> but, when user goes https://url.com/?dlink=link-2 it swaps out link new link <a id="dlink" href="link-2">link</a> first need value url. if you're not using javascript framework can create function using jquery. please check get url parameter jquery or how query string values in js it. after that, element "a" id , set value there. $("#dlink").attr("href", hreffromparam);
Read more

amazon web services - AWS Route53 Trying To Get Site To Resolve To www -

so have domain nameservers pointed aws route 53. site hosted on ec2 instance , reachable resolves site.com, if input www.site.com. i'm curious how site resolve www.site.com. have under route53 domains hosted zone 2 additional record sets outside of ns , soa: a site.com ec2 elastic ip cname www.site.com ec2 public dns i can reach site using both site.com , www.site.com, resolve site.com. there way make if enter either, resolves www.site.com? at first, both should record set. a site.com ec2 elastic ip www.site.com ec2 elastic ip and need redirect setting in web server (nginx, apache, ..) following. (source: how redirect www non-www nginx on centos 7 ) redirect non-www www if want redirect users plain, non-www domain www domain, add server block: server { server_name example.com; return 301 $scheme://www.example.com$request_uri; } save , exit. configures nginx redirect requests "example.com" "www.example.com". not...
Read more