0

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.

4 Answers 4

1

You can do this without any formulas by building a query using Get and Transform assuming you are on the latest version of Office on Windows.

Here is how to do it for the data you provided:

  1. First, put the original data in a table (select any cell in the range > ALT+N -> T) and ensure the year column is sorted from smallest to largest
  2. Next, make a query from the table (ALT+O -> PT)
  3. In the Query Editor, go to the Transform tab and select Pivot Column
  4. For the Values Column select Data
  5. On the Home tab select Close and Load

This outputs the following table:

The best part about this is when the original source changes, you just need to re-run the query with ALT+A -> R -> A and it will populate new data.

0

if the data is sorted then you could go with INDEX:INDEX and MATCH like for B2 (Sheet1 for the list and Sheet2 for formula asumed):
first set the range to look at via MATCH

MATCH(Sheet2!$A2,Sheet1!$A:$A,0)
MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1

this way you get the first and last row for the country. Now set the range to look at.

INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1)

The Output will look the same just for $C:$C.
To find the right row we use

MATCH(Sheet2!B$1,[Search range formula],0)

Put this in the full formula

INDEX([output range],[right row])

As this will error if no match is found also put an error part aound it

IFERROR([full formula],"")

all together it should look like this

=IFERROR(INDEX(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),MATCH(Sheet2!B$1,INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),0)),"")

This can be auto filled to left and down as you need it. Just add a line after the last line in both lists like "zzz" or the last country will not output anything.

enter image description here

Done by Phone and may contain errors.

0

Enter

=SUMPRODUCT((TRIM($A$2:$A$18)=TRIM($E3))*(TRIM($B$2:$B$18)=TRIM(F$2))*$C$2:$C$18)

in F3 and copy/drag it to fill the whole table (F3:P5 in the example):

enter image description here

This formula places 0, when there is no data, so you can easily use IF() function if you need to have empty cells instead.

0

Thanks everyone for the suggestions, however I actually managed to end up solving this using a pivot table. All that i had to do was create a pivot table. Set the columns to 'year', set the rows to 'country' and set the values to 'data'.

I then just had to set the 'field value setting' for data to 'max' and the table was populated. Took about 30 seconds in the end...

You can access the 'Field value setting' from the drop down menu on the 'data' field in the values box, within the Pivot Table Fields dialogue.

Thanks again for the answers I will definitely try the match, index formulas at some point.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .