{"id":20498,"date":"2022-11-09T21:18:14","date_gmt":"2022-11-09T15:48:14","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/"},"modified":"2022-11-09T21:18:14","modified_gmt":"2022-11-09T15:48:14","slug":"solved-vba-excel-tricky-nested-column-sorting-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/","title":{"rendered":"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-46045737\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"46045737\" data-parentid=\"45991635\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>If I was coding this myself (rather than trying to explain a method for someone else to code this) I would do the following:<\/p>\n<pre><code>Type ValueAndLevel\n    dataValue As String\n    dataLevel As Long\nEnd Type\n\nDim myData() As ValueAndLevel\n\nSub Export1()\n    ReDim myData(1 To 1)\n    'Start the process in column 1, with level being 0\n    Process 1, 0\n    'Get rid of the last dummy entry added to the array\n    ReDim Preserve myData(1 To UBound(myData) - 1)\nEnd Sub\n\nSub Process(c As Long, l As Long)\n    Dim vl As ValueAndLevel\n    Dim r As Long\n    Dim c1 As Long\n    'Start this column at row 2\n    r = 2\n    Do While Cells(r, c).Value &lt;&gt; \"\"\n        'Store this cell's details\n        vl.dataValue = Cells(r, c).Value\n        vl.dataLevel = l\n        myData(UBound(myData)) = vl\n        'Increase size of array ready for next value\n        ReDim Preserve myData(1 To UBound(myData) + 1)\n        'search for sublevels starting from the column to the right\n        c1 = c + 1\n        Do While Cells(1, c1).Value &lt;&gt; \"\"\n            If Cells(1, c1).Value = Cells(r, c).Value Then\n                'If this column's row 1 matches the value we are looking for,\n                'process the column (at a level one deeper than we were at)\n                Process c1, l + 1\n                'Don't look for any more matching values - assume only one per customer\n                Exit Do\n            End If\n            c1 = c1 + 1\n        Loop\n        'Get ready to process the next row in this column\n        r = r + 1\n    Loop\nEnd Sub\n<\/code><\/pre>\n<p>At the end of <code>Export1<\/code> having run, the <code>myData<\/code> array will contain the relevant values and their associated levels.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] If I was coding this myself (rather than trying to explain a method for someone else to code this) I would do the following: Type ValueAndLevel dataValue As String dataLevel As Long End Type Dim myData() As ValueAndLevel Sub Export1() ReDim myData(1 To 1) &#8216;Start the process in column 1, with level being 0 &#8230; <a title=\"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\" aria-label=\"More on [Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]\">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":[457,400,391,561,401],"class_list":["post-20498","post","type-post","status-publish","format-standard","hentry","category-solved","tag-algorithm","tag-excel","tag-loops","tag-sorting","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] VBA Excel tricky &quot;Nested&quot; Column Sorting [closed] - 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-vba-excel-tricky-nested-column-sorting-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] VBA Excel tricky &quot;Nested&quot; Column Sorting [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] If I was coding this myself (rather than trying to explain a method for someone else to code this) I would do the following: Type ValueAndLevel dataValue As String dataLevel As Long End Type Dim myData() As ValueAndLevel Sub Export1() ReDim myData(1 To 1) &#039;Start the process in column 1, with level being 0 ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-09T15:48:14+00:00\" \/>\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-vba-excel-tricky-nested-column-sorting-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]\",\"datePublished\":\"2022-11-09T15:48:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\"},\"wordCount\":59,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"algorithm\",\"excel\",\"loops\",\"sorting\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\",\"name\":\"[Solved] VBA Excel tricky \\\"Nested\\\" Column Sorting [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-11-09T15:48:14+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]\"}]},{\"@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=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] VBA Excel tricky \"Nested\" Column Sorting [closed] - 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-vba-excel-tricky-nested-column-sorting-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] VBA Excel tricky \"Nested\" Column Sorting [closed] - JassWeb","og_description":"[ad_1] If I was coding this myself (rather than trying to explain a method for someone else to code this) I would do the following: Type ValueAndLevel dataValue As String dataLevel As Long End Type Dim myData() As ValueAndLevel Sub Export1() ReDim myData(1 To 1) 'Start the process in column 1, with level being 0 ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/","og_site_name":"JassWeb","article_published_time":"2022-11-09T15:48:14+00:00","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-vba-excel-tricky-nested-column-sorting-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]","datePublished":"2022-11-09T15:48:14+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/"},"wordCount":59,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["algorithm","excel","loops","sorting","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/","name":"[Solved] VBA Excel tricky \"Nested\" Column Sorting [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-09T15:48:14+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-tricky-nested-column-sorting-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] VBA Excel tricky &#8220;Nested&#8221; Column Sorting [closed]"}]},{"@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=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","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\/20498","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=20498"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/20498\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=20498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=20498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=20498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}