I am trying to prepare an excel spreadsheet in which I move timeseries data which is in rows into columns when each category (countries in this case) has a different number of data points. Apologies in advance if I am not describing this perfectly.
I'll make this clear with an example.This is a mock-up of what I currently have:
Country Year Data Afghanistan 2000 10 Afghanistan 2004 12 Afghanistan 2006 15 Afghanistan 2010 30 Albania 2001 40 Albania 2002 50 Albania 2005 55 Albania 2006 70 Albania 2008 60 Albania 2009 60 Algeria 2000 23 Algeria 2005 30 Algeria 2006 40 Algeria 2007 41 Algeria 2008 39 Algeria 2009 35 Algeria 2010 40 ...
The data set is large and runs over a longer time period.
What I want the data set to look like is this:
Country 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 Afghanistan: 10 12 15 30 Albania: 40 50 55 70 60 60 Algeria: 23 30 40 41 39 35 40 ...
I can generate the structure that I want quite easily:
Country 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 Afghanistan Albania Algeria ...
But I cannot work out how to populate the table.I think that I need to do an hlookup or vlookup, moving from columns to rows, and it needs to handle multiple criteria for matching - 'country' + 'year' (I tried concatinating, but that made too many data points: Afghanistan2000, Afghanistan2001, etc etc.).
Also pivot table doesn't seem to work because it sums the data, and copying "if(and(" statements won't work because its columns to rows.
I have been all over the internet and can't work out the tool for this. Nonetheless I am sure there is a simple solution, but i can't seem to work it out.
If anyone can help it would be greatly appreciated as I get a lot of time series data in this format.