Hi everyone,

First time poster here. I've been hitting this issue time and again, and I'm sure I'm missing something, and that there must be an easier way to do this.

I have a downloaded data set, where my data values are split up into a set of columns for each measure. However Date is also a column (with each year referenced as value in the row), whereas I'd like each of the available values to be a column.

i.e.

Downloaded data

Facility | Year | Measure | Confidence
1 | 2010 | 34.4 | High
1 | 2011 | 23.2 | High
1 | 2012 | 16.2 | Medium
2 | 2010 | 54.2 | Low
2 | 2011 | 43.2 | Low
2 | 2012 | 65.1 | Medium
3 | 2010 | 43.3 | Low
etc.

and I want to turn it into:

Facility | Measure - 2010 | Measure - 2011 | Measure 2012 | Confidence 2010 | Confidence 2011 | Confidence 2012
1 | 34.4 | 23.2 | 16.2 | High | High | Medium
2 | 54.2 | 43.2 | 65.1| Low | Low | Medium
3 etc.


I know I can do this with a pivot table, and re-arrange the fields so that Year becomes a column label and my other indicator values are Values. However, it seems I must always perform a mathematical operation on the Values. In my case it's not really summarsing anything at all (there will always be just a single value), and whereas this works by simply doing a Sum on numerics (The sum of 1 record is just that same value), it doesn't work for a categoric classification.

My work-around for now is to not use pivot tables, but simply filter my source data on Date. Paste each date combination into a new workbook and then do a VLOOKUP to recombine my values together on the facility code but into different columns per year..

There must be an easier way??

Help!