Transform badly formatted excel data into a form for pivot tables

How ca开发者_开发技巧n I transform data in excel in this form (see image here: http://imgur.com/PgX5r) into something suitable for using in a pivot chart. I need three columns eventually, Date, Country and Score. The amount of data points for each country is not constant.

The easiest way i found without using VBA is to use an array formula :

• Add another column on the left of your values (let say Col A)
• Set this formula on cell A1: {=INDEX(B:B,MAX(IF(ISEMPTY($C$1:C1),ROW($C$1:C1),ROW($C$1))))}
• Validate with CtrlShiftEnter
• Drag the formula till the end of your values

Your data are almost suitable for a Pivot Chart. What you can do next is to delete unneeded rows (with country names and headers) or use another formula on another sheet to "duplicate" the data except when unwanted rows (you can then filter the blank data in the pivot chart).