Excel Transpose 1 Dimensional Array into a Multidimensional Array












-1















I need to adjust my data so it aligns with the rest,



This is my data, and the trailing off numbers need to be put in a (12 by x) matrix



Picture



I have been doing this by hand, so the next line looks like this:



This was done via copy/paste



And I want to vertically transpose the rest of these numbers that are currently horizontal by groups of 12 so I do not have to do it by hand.



These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix:
These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix










share|improve this question

























  • I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

    – JNevill
    Nov 19 '18 at 21:15











  • Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

    – John Gustafson
    Nov 19 '18 at 21:41











  • You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

    – Michal Rosa
    Nov 20 '18 at 3:04











  • Does this help? I edited the original post.

    – John Gustafson
    Nov 20 '18 at 12:42











  • Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

    – Luuklag
    Nov 20 '18 at 13:00


















-1















I need to adjust my data so it aligns with the rest,



This is my data, and the trailing off numbers need to be put in a (12 by x) matrix



Picture



I have been doing this by hand, so the next line looks like this:



This was done via copy/paste



And I want to vertically transpose the rest of these numbers that are currently horizontal by groups of 12 so I do not have to do it by hand.



These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix:
These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix










share|improve this question

























  • I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

    – JNevill
    Nov 19 '18 at 21:15











  • Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

    – John Gustafson
    Nov 19 '18 at 21:41











  • You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

    – Michal Rosa
    Nov 20 '18 at 3:04











  • Does this help? I edited the original post.

    – John Gustafson
    Nov 20 '18 at 12:42











  • Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

    – Luuklag
    Nov 20 '18 at 13:00
















-1












-1








-1








I need to adjust my data so it aligns with the rest,



This is my data, and the trailing off numbers need to be put in a (12 by x) matrix



Picture



I have been doing this by hand, so the next line looks like this:



This was done via copy/paste



And I want to vertically transpose the rest of these numbers that are currently horizontal by groups of 12 so I do not have to do it by hand.



These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix:
These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix










share|improve this question
















I need to adjust my data so it aligns with the rest,



This is my data, and the trailing off numbers need to be put in a (12 by x) matrix



Picture



I have been doing this by hand, so the next line looks like this:



This was done via copy/paste



And I want to vertically transpose the rest of these numbers that are currently horizontal by groups of 12 so I do not have to do it by hand.



These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix:
These are the 'trailing off' numbers that need to be vertically transposed by groups of 12, so into a 12 by ?? matrix







excel transpose






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 20 '18 at 12:59









Luuklag

3,37792742




3,37792742










asked Nov 19 '18 at 21:08









John GustafsonJohn Gustafson

11




11













  • I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

    – JNevill
    Nov 19 '18 at 21:15











  • Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

    – John Gustafson
    Nov 19 '18 at 21:41











  • You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

    – Michal Rosa
    Nov 20 '18 at 3:04











  • Does this help? I edited the original post.

    – John Gustafson
    Nov 20 '18 at 12:42











  • Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

    – Luuklag
    Nov 20 '18 at 13:00





















  • I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

    – JNevill
    Nov 19 '18 at 21:15











  • Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

    – John Gustafson
    Nov 19 '18 at 21:41











  • You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

    – Michal Rosa
    Nov 20 '18 at 3:04











  • Does this help? I edited the original post.

    – John Gustafson
    Nov 20 '18 at 12:42











  • Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

    – Luuklag
    Nov 20 '18 at 13:00



















I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

– JNevill
Nov 19 '18 at 21:15





I'm not following. You say "Matrix" and "Multidimensional Array" but those aren't the same thing. I don't know what "Trailing off numbers" means in regards to your picture. Can you share more info without the technical jargon (unless you literally mean a multidimensional array or a matrix).

– JNevill
Nov 19 '18 at 21:15













Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

– John Gustafson
Nov 19 '18 at 21:41





Apologies, the trailing off numbers refer to Row 5 which continues on for +100 cells past column Q. So it currently exists as a 1 by 100-somthing matrix, and i need it to be a 12 by something matrix

– John Gustafson
Nov 19 '18 at 21:41













You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

– Michal Rosa
Nov 20 '18 at 3:04





You need to provide step by step explanation of what is your input, what is your desired output and what do you want to do. As it stands at the moment, your question does not make much sense.

– Michal Rosa
Nov 20 '18 at 3:04













Does this help? I edited the original post.

– John Gustafson
Nov 20 '18 at 12:42





Does this help? I edited the original post.

– John Gustafson
Nov 20 '18 at 12:42













Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

– Luuklag
Nov 20 '18 at 13:00







Your question is too broad as it stand, but this is a typical job for VBA. You should look into the concepts of LastColumn and Looping. A fairly simple script should then do the job.

– Luuklag
Nov 20 '18 at 13:00














1 Answer
1






active

oldest

votes


















0














You can use the formula as below



I have made the groups of 6 because to show the image properly but you can change that to 12



enter image description here




  • A3 to F3 is index you can increase it to 12

  • A1 to AT1 is the series to evaluate, you will have your own

  • in cell A4 the formula is =INDEX($A$1:$AT$1,1,6*(ROW()-4)+A$3)

  • In your case 6*(ROW()-4) will be 12*(row() - (row number where the formula is to be inserted)






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382652%2fexcel-transpose-1-dimensional-array-into-a-multidimensional-array%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    You can use the formula as below



    I have made the groups of 6 because to show the image properly but you can change that to 12



    enter image description here




    • A3 to F3 is index you can increase it to 12

    • A1 to AT1 is the series to evaluate, you will have your own

    • in cell A4 the formula is =INDEX($A$1:$AT$1,1,6*(ROW()-4)+A$3)

    • In your case 6*(ROW()-4) will be 12*(row() - (row number where the formula is to be inserted)






    share|improve this answer




























      0














      You can use the formula as below



      I have made the groups of 6 because to show the image properly but you can change that to 12



      enter image description here




      • A3 to F3 is index you can increase it to 12

      • A1 to AT1 is the series to evaluate, you will have your own

      • in cell A4 the formula is =INDEX($A$1:$AT$1,1,6*(ROW()-4)+A$3)

      • In your case 6*(ROW()-4) will be 12*(row() - (row number where the formula is to be inserted)






      share|improve this answer


























        0












        0








        0







        You can use the formula as below



        I have made the groups of 6 because to show the image properly but you can change that to 12



        enter image description here




        • A3 to F3 is index you can increase it to 12

        • A1 to AT1 is the series to evaluate, you will have your own

        • in cell A4 the formula is =INDEX($A$1:$AT$1,1,6*(ROW()-4)+A$3)

        • In your case 6*(ROW()-4) will be 12*(row() - (row number where the formula is to be inserted)






        share|improve this answer













        You can use the formula as below



        I have made the groups of 6 because to show the image properly but you can change that to 12



        enter image description here




        • A3 to F3 is index you can increase it to 12

        • A1 to AT1 is the series to evaluate, you will have your own

        • in cell A4 the formula is =INDEX($A$1:$AT$1,1,6*(ROW()-4)+A$3)

        • In your case 6*(ROW()-4) will be 12*(row() - (row number where the formula is to be inserted)







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 20 '18 at 16:14









        usmanhaqusmanhaq

        1,113128




        1,113128
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382652%2fexcel-transpose-1-dimensional-array-into-a-multidimensional-array%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()