{"id":9334,"date":"2022-09-18T11:10:47","date_gmt":"2022-09-18T05:40:47","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/"},"modified":"2022-09-18T11:10:47","modified_gmt":"2022-09-18T05:40:47","slug":"solved-how-can-i-transpose-this-data-set-into-this-specific-order","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/","title":{"rendered":"[Solved] How can I transpose this data set into this specific order?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-54253406\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"54253406\" data-parentid=\"54252775\" data-score=\"1\" 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>Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed.<\/p>\n<pre><code>Sub TransposeData()\n\n    Const FirstDataRow As Long = 2              ' presuming row 1 has headers\n    Const YearColumn As String = \"A\"            ' change as applicable\n\n    Dim Rng As Range\n    Dim Arr As Variant, Pos As Variant\n    Dim Rl As Long, Cl As Long\n    Dim R As Long, C As Long\n    Dim i As Long\n\n    With ActiveSheet\n        Cl = .UsedRange.Columns.Count - .UsedRange.Column + 1\n        Rl = .Cells(.Rows.Count, Columns(YearColumn).Column).End(xlUp).Row\n        Set Rng = Range(.Cells(FirstDataRow, YearColumn), .Cells(Rl, Cl))\n    End With\n    Arr = Rng.Value\n    ReDim Pos(1 To (UBound(Arr) * UBound(Arr, 2)), 1 To 2)\n\n    For R = 1 To UBound(Arr)\n        For C = 2 To UBound(Arr, 2)\n            i = i + 1\n            Pos(i, 1) = Arr(R, 1)\n            Pos(i, 2) = Arr(R, C)\n        Next C\n    Next R\n\n    R = Rl + 5                                  ' write 5 rows below existing data\n    Set Rng = ActiveSheet.Cells(R, YearColumn).Resize(i, 2)\n    Rng.Value = Pos\nEnd Sub\n<\/code><\/pre>\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 How can I transpose this data set into this specific order? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed. Sub TransposeData() Const FirstDataRow As Long = 2 &#8216; presuming row 1 has headers Const YearColumn As String = &#8220;A&#8221; &#8230; <a title=\"[Solved] How can I transpose this data set into this specific order?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\" aria-label=\"More on [Solved] How can I transpose this data set into this specific order?\">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,2635,2273,401],"class_list":["post-9334","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-excel-2016","tag-transpose","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] How can I transpose this data set into this specific order? - 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-how-can-i-transpose-this-data-set-into-this-specific-order\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How can I transpose this data set into this specific order? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed. Sub TransposeData() Const FirstDataRow As Long = 2 &#039; presuming row 1 has headers Const YearColumn As String = &quot;A&quot; ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-18T05:40:47+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-how-can-i-transpose-this-data-set-into-this-specific-order\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How can I transpose this data set into this specific order?\",\"datePublished\":\"2022-09-18T05:40:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\"},\"wordCount\":60,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"excel-2016\",\"transpose\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\",\"name\":\"[Solved] How can I transpose this data set into this specific order? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-18T05:40:47+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How can I transpose this data set into this specific order?\"}]},{\"@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] How can I transpose this data set into this specific order? - 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-how-can-i-transpose-this-data-set-into-this-specific-order\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How can I transpose this data set into this specific order? - JassWeb","og_description":"[ad_1] Try this code, but before you do adjust the two constants at the top to match the facts on your worksheet. The worksheet with the data must be active when the code is executed. Sub TransposeData() Const FirstDataRow As Long = 2 ' presuming row 1 has headers Const YearColumn As String = \"A\" ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/","og_site_name":"JassWeb","article_published_time":"2022-09-18T05:40:47+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-how-can-i-transpose-this-data-set-into-this-specific-order\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How can I transpose this data set into this specific order?","datePublished":"2022-09-18T05:40:47+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/"},"wordCount":60,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","excel-2016","transpose","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/","url":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/","name":"[Solved] How can I transpose this data set into this specific order? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-18T05:40:47+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-can-i-transpose-this-data-set-into-this-specific-order\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How can I transpose this data set into this specific order?"}]},{"@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\/9334","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=9334"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/9334\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=9334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=9334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=9334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}