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!!