I would be very careful using Pivot Tables. I have had several very bad results where they did not faithfully return the right values, sums were incorrect, and a host of other things.
This would be my suggestion for how to handle this, and will work for any version of Excel.
The first thing that you need is a Unique Key so that you can look up the value in your data set. Insert a column before ID, and then add the formula
=B2&C2 into cell
A2. Then just copy that down the entire column and you will have an identifier that looks like
Next you want to name your data range. The easiest way to do this is to is to highlight the entire data set from
Dx, click on the small box just above column A and type in
Data. This will name your range. Your data will now look like this:
ID TEST VALUE
ID001TEST1 ID001 TEST1 25
ID001TEST2 ID001 TEST2 30
ID001TEST3 ID001 TEST3 54
ID002TEST1 ID002 TEST1 23
ID002TEST2 ID002 TEST2 54
ID002TEST3 ID002 TEST3 67
Now create a new sheet and enter in the column headers and the participant id something like this
A B C D
1 ID TEST1 TEST2 TEST3
Once you have that you are going to create the following formula and paste into
Basically the first argument creates the unique key from concatenating the participant id in column A and the column headings in row 1. The dollar signs fix the column to A and the row to one for the second part so that when you copy and paste the formula, it will make the unique key that you want to look up. You can now paste that formula into the body of your table and it should give you every value correctly.
The second argument is the name of your data set that you set before. The benefit of using the name is that when you copy and paste it doesn't upset the cell references.
The third argument, 4, represents the value row and this is where the function will pull the data for what it matches to the lookup key.
The last argument, 0 tells the lookup function to pass back an #N/A if there is no value found for that key.
Your final result will look like this:
ID TEST1 TEST2 TEST3
ID001 25 30 54
ID002 23 54 67
The thing that you have to remember is that whether you are doing a sort or querying of a flat file database, you need to have a unique data key to be able to sort or look up. The trick is that by concatenating two or three or the data point, you can usually create a unique key if one isn't already provided for you. That is the magic, everything else just becomes how you format the results.