Announcement

Collapse
No announcement yet.

Needing spreadsheet reformatted into columnar format for pivot table $20USD

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Needing spreadsheet reformatted into columnar format for pivot table $20USD



    The short explanation: I need VBA to reformat this report (original format.xls) into a single columnar format so I may create a pivot table incorporating all of the properties. Please see "the original format.xlsx" and the "desired format.xlsx". The desired format has two properties on it as an example, but I'd like all the properties to be consolidated in a similar way please, so I may create a pivot table from all the properties.


    The longer explanation: I get a spreadsheet that has a number of properties on it as an output from a program into a spreadsheet. Every property needs to essentially be reversed pivot tabled and consolidated with the VBA code please. The "property name" is listed once above each cash flow as is the "category" of the property. Sometimes the columns may grow in the number to the right, depending upon what fields I choose in the software, the the top list of columns is the same for all properties. 90% of all the all properties will be consistent all the way down as you can see in "original format". There are a 3 exceptions property types you will see in the "original format.xlsx"

    1) the data around row 318 for example, this individual property runs uneconomic after 1 month. You can just delete the words "non-economic"; then reformat it like any other property. these can happen randomly through the list. I highlighted yellow.

    2) the data from row 454-597 for example, is a subtotal for the properties above it, please use the property name as row 464, and category as row 465, then reformat it like any other property. There a few subtotals on the report. I highlighted green.

    3) the last property in the “original format.xls’ is a grand total of all properties, you can use row 602 as the property name and row 609 as the category. I highlighted blue.




    Any help would really be appreciated thank you!!
    Attached Files

  • #2
    I can look at this for you.
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Comment


    • #3
      Did you manually make all those merged cells on the original format sheet?
      We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

      Comment


      • #4
        Your file is ready. I will PM you with payment details.
        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

        Comment


        • #5


          Payment received - many thanks.

          Your sample file is attached. I removed all the merged cells (merged cells should be avoided at all costs, they are a disater waiting to happen when you try to manipulate the data!). I also made the reformatted data appear in an Excel built-in table, this will make it easier for you to create your pivot table(s). For a pivot table source data you need only enter the table name (tblData in this case) instead of entering the specific range of a manually created table.

          Click the button on the Raw_Data sheet.
          Attached Files
          We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

          Comment

          Working...
          X