2 Replies Latest reply: May 15, 2014 10:03 AM by BethG _ RSS

    Make my Column Headers in to Rows

    BethG _



      We are in a bind! We are trying to find a way to take a fairly simple Summary export from a project, and combine it with a previously exported file from that same project. The files (excel) look similar to this with one row (although, ours have about 20 columns, not 5):


      Last month’s Export:


      Report Date     Total AR Calc          >90          Credits Over 90          20% Cross Age

      12/31/08     2,056.85          128.71          56.31               0.00


      This month’s Export:


      Report Date      Total AR Calc          >90          Credits Over 90          20% Cross Age

      1/31/09          900.85               321.17          112.02               15.00


      What we need to do, is combine the two simple summaries into something that looks

      like this next:


                     12/31/08          1/31/09               Difference

      Total AR Calc          2,056.85          900.85               (1,156.00)

      >90               128.71               321.17               192.46

      Credits Over 90          56.31               112.02               55.71

      20% Cross Age          0.00               15.00               15.00[/CODE]


      I know how to make the Key Fields sort across as the headers. But, is there a way to bring our column headers in to the rows of the following project while still being able to carry the information they have below them?


      Thank you!

        • Make my Column Headers in to Rows
          Data Kruncher

          Hi Beth,


          While you might[/I] be able to accomplish this with Monarch, though I haven't entirely convinced myself that it's possible, I believe that developing the model would require a lot of effort, and achieving the same end result with Excel would be much easier. Hopefully you're not using Excel 2007 as I don't know if what follows here will work in that case.


          By the sounds of this, you want an on-going month over month comparison analysis. So you'll only ever have a row of heading, and two rows of monthly total amounts.


          Export the Month 1 data to a Excel file (overwrite existing file), then append the Month data 2 with a separate project export.


          Now open a new Excel file, and hit Alt-F11 to go to the Visual Basic Editor


          On the left side of the window you should see the VBAProject /Bfor your new file. Double-click the ThisWorkbook item. Into the white space on the right, paste this code:


          Private Sub Workbook_Open()

              Workbooks.Open Filename:= _

                  "C:\Exports\Monthly Data.xls"




              Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                  :=False, Transpose:=True


              Application.CutCopyMode = False

              Selection.NumberFormat = "m/d/yy;@"


              ActiveCell.FormulaR1C1 = "Difference"


              ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"



              Selection.Style = "Comma"




          End Sub



          Most of this was created with Excel's macro recorder, so it's not the best code ever written, but we won't worry about such things for such a small use.


          The only thing that you'll need to do is change the file path and name of your export file in the code, and make changes to accomodate for your 20 columns. But the approach is the same.


          The program code will run automatically every time you open this file. Quick and hands-free.


          Or we can continue to attempt a Monarch only solution.




            • Make my Column Headers in to Rows
              BethG _

              Hi Kruncher -

              Thank you for your help and the great VBScript info. It is working perfectly after some minor tweaking.

              Unfortunately, we currently run 3 projects to get the summary to this point, and we do this for a couple hundred customers every month. So, the more I can get automated with Monarch, the happier I am (especially if we can get datapump going soon!).


              Do you know if Datapump can run the excel macros like this one?