PDA

View Full Version : Tediously adding data to a table year-over-year, looking for pivot ideas..



threesixzero
2017-03-12, 06:29 PM
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:





2008
2009
2010


AL
A
A
A


AL
B
B



AK
C
C
C


AZ

G
G


CA
D
D
D


CA
E
E
E


FL
F
F
F


GA


H






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?

p45cal
2017-03-16, 12:25 AM
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.

threesixzero
2017-03-25, 10:20 PM
thanks for your help! very useful since im starting to learn about macros