There used to be a time when I thought I knew what I was doing in Excel…
Anyway, here’s what I want to do, as described by how I’ve been doing it.
Sheet 1
This is all the original data. It’s a list of transactions over 4 years, with funds going to various categories. Many people have made multiple transactions over each year.
Year Name Fund Amount Type
2009 Adam House 100 A
2009 Adam Car 100 A
2009 Betty House 100 B
2010 Adam House 200 A
2010 Adam Car 50 A
2010 Betty House 100 B
2010 Betty Patio 500 B
2011 Adam House 200 A
2011 Betty House 300 B
2011 Betty Car 100 B
2011 Betty House 100 B
2011 Chuck Car 50 A
2011 David Patio 100 B
2012 Adam House 200 A
2012 Betty Car 100 B
2012 David Patio 100 B
2012 David House 100 B
Sheet 2
This was data copied over from a pivot chart that consolidated all data from each person per year. The problem is that the “TYPE” field screws up the pivot, and I need to copy it in by hand (essentially).
Year Name Amount Type
2009 Adam 200 A
2009 Betty 100 B
2010 Adam 250 A
2010 Betty 600 B
2011 Adam 200 A
2011 Betty 500 B
2011 Chuck 50 A
2011 David 100 B
2012 Adam 200 A
2012 Betty 100 B
2012 David 200 B
Sheet 3
This is data that I can run easily from a pivot from Sheet 2. That much I know!
So how would you generate Sheet 2?