Excel - Change extra columns to rows












0















I get a file with the numbers for a variety of different products and how many have been returned each month.



Raw Data



To make the data neater, I want to split this evenly by day. I have written some formulas to do this in Excel, but it now leaves me with lots of columns for each month.



Transformed Data



Instead of the same number of rows with multiple columns, I want a row for each date and product combination with one column for products returned and one column for the value of the return. I have turned my progress into a Google Sheet here



Final Data



Can anyone help me fill in this final sheet? It will need the date and product repeated the number of times of the number of days in the month, and then the right values put in the next two columns.










share|improve this question



























    0















    I get a file with the numbers for a variety of different products and how many have been returned each month.



    Raw Data



    To make the data neater, I want to split this evenly by day. I have written some formulas to do this in Excel, but it now leaves me with lots of columns for each month.



    Transformed Data



    Instead of the same number of rows with multiple columns, I want a row for each date and product combination with one column for products returned and one column for the value of the return. I have turned my progress into a Google Sheet here



    Final Data



    Can anyone help me fill in this final sheet? It will need the date and product repeated the number of times of the number of days in the month, and then the right values put in the next two columns.










    share|improve this question

























      0












      0








      0








      I get a file with the numbers for a variety of different products and how many have been returned each month.



      Raw Data



      To make the data neater, I want to split this evenly by day. I have written some formulas to do this in Excel, but it now leaves me with lots of columns for each month.



      Transformed Data



      Instead of the same number of rows with multiple columns, I want a row for each date and product combination with one column for products returned and one column for the value of the return. I have turned my progress into a Google Sheet here



      Final Data



      Can anyone help me fill in this final sheet? It will need the date and product repeated the number of times of the number of days in the month, and then the right values put in the next two columns.










      share|improve this question














      I get a file with the numbers for a variety of different products and how many have been returned each month.



      Raw Data



      To make the data neater, I want to split this evenly by day. I have written some formulas to do this in Excel, but it now leaves me with lots of columns for each month.



      Transformed Data



      Instead of the same number of rows with multiple columns, I want a row for each date and product combination with one column for products returned and one column for the value of the return. I have turned my progress into a Google Sheet here



      Final Data



      Can anyone help me fill in this final sheet? It will need the date and product repeated the number of times of the number of days in the month, and then the right values put in the next two columns.







      excel






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 21 '18 at 16:56









      AlexAlex

      11




      11
























          1 Answer
          1






          active

          oldest

          votes


















          0














          This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):



          enter image description here



          Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)



          Moving onto the new sheet:



          enter image description here



          Column A is a list of dates repeated X times for each product in that month.
          I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:



          =IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)


          Column B looks up the product by seeing how many times a given date has been repeated thus far:



          =INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)


          Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          Column D is the same as C but gets the total product value instead:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.



          Thanks!






          share|improve this answer
























          • Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

            – Alex
            Nov 22 '18 at 11:22













          • I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

            – Alex
            Nov 23 '18 at 16:49











          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%2f53417053%2fexcel-change-extra-columns-to-rows%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














          This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):



          enter image description here



          Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)



          Moving onto the new sheet:



          enter image description here



          Column A is a list of dates repeated X times for each product in that month.
          I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:



          =IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)


          Column B looks up the product by seeing how many times a given date has been repeated thus far:



          =INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)


          Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          Column D is the same as C but gets the total product value instead:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.



          Thanks!






          share|improve this answer
























          • Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

            – Alex
            Nov 22 '18 at 11:22













          • I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

            – Alex
            Nov 23 '18 at 16:49
















          0














          This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):



          enter image description here



          Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)



          Moving onto the new sheet:



          enter image description here



          Column A is a list of dates repeated X times for each product in that month.
          I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:



          =IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)


          Column B looks up the product by seeing how many times a given date has been repeated thus far:



          =INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)


          Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          Column D is the same as C but gets the total product value instead:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.



          Thanks!






          share|improve this answer
























          • Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

            – Alex
            Nov 22 '18 at 11:22













          • I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

            – Alex
            Nov 23 '18 at 16:49














          0












          0








          0







          This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):



          enter image description here



          Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)



          Moving onto the new sheet:



          enter image description here



          Column A is a list of dates repeated X times for each product in that month.
          I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:



          =IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)


          Column B looks up the product by seeing how many times a given date has been repeated thus far:



          =INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)


          Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          Column D is the same as C but gets the total product value instead:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.



          Thanks!






          share|improve this answer













          This solution uses array formulas. It may not be the best solution, but it works (albeit very slowly). I first created a summary table (you could workaround this step if you wanted to, but I think it makes the solution cleaner):



          enter image description here



          Columns A:D are your initial product table. Column F is a list of every date, this could be created manually or with a formula. Column G is =COUNTIF(A:A,F2)



          Moving onto the new sheet:



          enter image description here



          Column A is a list of dates repeated X times for each product in that month.
          I started by manually writing "6/1/2018" a few times then switched to this formula, which looks for the count of products for a given month and checks to see if the date has been repeated that many times already. If it has, it adds one additional day. Otherwise, it keeps repeating:



          =IF(COUNTIF(A$2:A6,A6)=INDEX(Sheet3!G:G,MATCH(DATE(YEAR(A6),MONTH(A6),1),Sheet3!F:F,0)),A6+1,A6)


          Column B looks up the product by seeing how many times a given date has been repeated thus far:



          =INDIRECT("Sheet3!B"&MATCH(DATE(YEAR(A2),MONTH(A2),1),Sheet3!A:A,0)+COUNTIF(A$2:A2,A2)-1)


          Column C is an array formula (commit it with Ctrl + Shift + Enter), which gets the total products and divides by number of days in each month:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          Column D is the same as C but gets the total product value instead:



          =INDEX(Sheet3!C:C,MATCH(1,INDEX((B2=Sheet3!B:B)*(DATE(YEAR(A2),MONTH(A2),1)=Sheet3!A:A),0,1),0))/DAY(EOMONTH(A2,0))


          I realize that these may be hard to follow. I'd be happy to send you the workbook if you give me your email address. Also, someone else may have an easier to implement solution.



          Thanks!







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 21 '18 at 17:42









          extensionhelpextensionhelp

          22510




          22510













          • Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

            – Alex
            Nov 22 '18 at 11:22













          • I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

            – Alex
            Nov 23 '18 at 16:49



















          • Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

            – Alex
            Nov 22 '18 at 11:22













          • I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

            – Alex
            Nov 23 '18 at 16:49

















          Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

          – Alex
          Nov 22 '18 at 11:22







          Thank you so much, this looks amazing. I figured an Array formula was needed, but I haven't really got my head around them. I will implement this and then add any more feedback. On the off chance any good resources you could recommend on array formulas? And I may take you up on the workbook offer, but even more than a solution, I'd love to be to understand it, and reuse it in different formats for other issues.

          – Alex
          Nov 22 '18 at 11:22















          I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

          – Alex
          Nov 23 '18 at 16:49





          I'd love you to email me your workbook, but can't see a way to send you my email address without posting it here live for the world to see. Am I missing something? I was hoping to lookup the values from my intermediate sheet, as I made sure I kept the values correct by making the ones at the beginning of the month, one higher than the rounded down average, to mainatin sensible looking numbers.

          – Alex
          Nov 23 '18 at 16:49




















          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%2f53417053%2fexcel-change-extra-columns-to-rows%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()