Tediously adding data to a table year-over-year, looking for pivot ideas..

threesixzero

New member
Joined
Mar 12, 2017
Messages
2
Reaction score
0
Points
0
Excel Version(s)
365
I'm being tasked to provide a list of properties that a company owns, changing year over year, in tabular form. I have to source this info from the company's 10Ks, so its not all in one location and requires quite a bit of having to copy and paste the data (State and Property Name) into Excel for formatting before being able to add it to the table year over year. My manager wants to see it by year at the top (year of the 10K used), states on the left (the state rows increase as new properties get added but remain if properties are removed to show history over time). In the format he's looking for, with north of 10+ years of data, the table is getting enormous and I have to be extra cautious not to make any mistakes with the formatting (adding rows as necessary, etc). See example of what the table would appear like after adding year-over-year data across 3 years. The letters represent property names.. so by year 3 (2010) it shows the company still owns property A in AL, property B in AL was owned until 2009, property G has been owned since 2009, etc:


200820092010
ALAAA
ALBB
AKCCC
AZGG
CADDD
CAEEE
FLFFF
GAH




I'd love to be able to paste the data I need into 3 columns (YEAR, STATE, PROPERTY) and then just pivot the entire set to show the list of properties over time but obv this isn't a pivot table's purpose (showing text in the values area).. and without macros I'm not sure there's a way to do this. I tried using PowerQuery but it displays errors for states with more than two properties using the "Do Not Aggregate" option. I feel as though there's a much easier way of accomplishing this without having to manually manicure the entire sheet to fit new yearly data each time. Anyone have any ideas?
 
I'd love to be able to paste the data I need into 3 columns (YEAR, STATE, PROPERTY) and then just pivot the entire set to show the list of properties over time but obv this isn't a pivot table's purpose (showing text in the values area).. and without macros I'm not sure there's a way to do this.
Nor am I, so with a macro, click the button in the attached.
 

Attachments

  • ExcelGuru7557.xlsm
    20.8 KB · Views: 10
Last edited:
thanks for your help! very useful since im starting to learn about macros
 
Back
Top