0

I work on a project, and I have to reformat a large data set of lab results. Now, each lab test is connected to the ID of a study participant - one row per test. I need to have the data reformatted so that each participant only has one row and one column for each of the up to 26 tests.

NOW:

ID....TEST....VALUE  
ID001 TEST1 25     
ID001 TEST2 30  
ID001 TEST3 54  
ID002 TEST1 23   
ID002 TEST2 54  
ID002 TEST3 67  
etc          

GOAL:

ID  TEST1 TEST2 TEST  
ID001 25 30 54  
ID002 23 54 67  
etc  

Is this doable in Excel 2010? Any help would be greatly appreciated.

  • Thank you all for the extremely quick help! I will try what you have suggested as soon as I can. – Henrik Jul 13 '15 at 23:03
2

This is a typical job people do with pivot table:

  • select your data and insert a pivot table
  • add IDs as row labels
  • add TESTs as column headings
  • add numbers to the body of the pivot, either use sum or average, as you have only one row outer item in the origins ask days both will be the same
0

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 ID001TEST1.

Next you want to name your data range. The easiest way to do this is to is to highlight the entire data set from A1 to 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
2  ID001    
3  ID002    

Once you have that you are going to create the following formula and paste into B2

=VLOOKUP($A2&B$1,Data,4,0)

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.

  • Nice approach. However I've never seen any pivot table wrong due to Excel, but it was wrong configuration. Using formula has even bigger chance of mistakes. Also with this row and column headers need to be updated manually. – Máté Juhász Jul 9 '15 at 7:37
  • Worked with Excel in Finance for 15 years, I saw plenty of times when pivot tables gave the wrong answers and have the shoeings for it. It was a little bit better if you used the functionality in Visual Basic, but for this that would have been overkill. – AMR Jul 9 '15 at 13:39
  • As long as the data is as consistent as @Henrik showed, then with Auto Fill, The framework for the output will take no time at all. With VB or Python you can automate the entire process. Formulas in Excel are always the most reliable way to go and are also implemented in such a way that they are much faster from a processing standpoint. The way I explained for him to set up the formula basically means that you can't get an input mistake. There is one area, the named range, that can bite you, but if you set it properly, it is your friend. – AMR Jul 9 '15 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.