{"id":11748,"date":"2022-09-28T10:52:03","date_gmt":"2022-09-28T05:22:03","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/"},"modified":"2022-09-28T10:52:03","modified_gmt":"2022-09-28T05:22:03","slug":"solved-sorting-a-range-of-values-correctly-in-vba","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/","title":{"rendered":"[Solved] Sorting a range of values correctly in vba"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort on in your pivottable. Conditional formatting can then do your colouring as well based on the helper column value.<\/p>\n

\n

Data with helper column and vlookup table:<\/strong><\/p>\n

\"Data\"<\/a><\/p>\n

Note that this would be your actual backing data before<\/strong> aggregating up that would have the helper column.<\/h2>\n

Pivot using helper column to order (note: you would use Count not Sum):<\/strong><\/p>\n

\"Pivot\"<\/a><\/p>\n

\n

Conditional formatting:<\/strong><\/p>\n

Add a conditional formatting rule (to meet your requirements) using the helper column value:<\/p>\n

\"Helper<\/a><\/h2>\n

Result:<\/strong><\/p>\n

\"Sorted<\/a><\/p>\n<\/p><\/div>\n

<\/div>\n<\/div>\n

5<\/span> <\/p><\/div>\n<\/div>\n

[ad_2]<\/p>\n

solved Sorting a range of values correctly in vba <\/p>\n","protected":false},"excerpt":{"rendered":"

[ad_1] Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,401],"class_list":["post-11748","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"\n[Solved] Sorting a range of values correctly in vba - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Sorting a range of values correctly in vba - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T05:22:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Sorting a range of values correctly in vba\",\"datePublished\":\"2022-09-28T05:22:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\"},\"wordCount\":147,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png\",\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\",\"name\":\"[Solved] Sorting a range of values correctly in vba - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png\",\"datePublished\":\"2022-09-28T05:22:03+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Sorting a range of values correctly in vba\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Sorting a range of values correctly in vba - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Sorting a range of values correctly in vba - JassWeb","og_description":"[ad_1] Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/","og_site_name":"JassWeb","article_published_time":"2022-09-28T05:22:03+00:00","og_image":[{"url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png","type":"","width":"","height":""}],"author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Sorting a range of values correctly in vba","datePublished":"2022-09-28T05:22:03+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/"},"wordCount":147,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png","keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/","url":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/","name":"[Solved] Sorting a range of values correctly in vba - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage"},"image":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png","datePublished":"2022-09-28T05:22:03+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#primaryimage","url":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/uploads\/2022\/09\/Solved-Sorting-a-range-of-values-correctly-in-vba.png"},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sorting-a-range-of-values-correctly-in-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Sorting a range of values correctly in vba"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11748","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=11748"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11748\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11748"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11748"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11748"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}