a special sorting dataset in excel

Hi all friends,

I have a large excel sheet (2010) with two columns and thousands of rows for 3 samples. Please take a look at the attached file, which is the short example of my dataset (Original dataset, left side of file). However, I would like to have a dataset like what I showed in the right side of the attached file (Desired dataset). Actually, all IDs should be located in one column and their corresponding values from various samples have to be put in other columns and if a ID has not any value in the sample, it should be shown with 0. Also, some of original IDs, like AT5G01600.1, in the sample 2 are duplicated or even triplicated with different values. In this case, the mean value must be put for the corresponding ID, for example, 4.76 for sample 2, in the right side of file. I need an easy way to do this task for my large original dataset. Could you please help me out in this regard?

Thank you in advance

Hi Mary,

You can accomplish this with the Consolidate function via the Data menu/ribbon.

These links will show you how.

https://msdn.microsoft.com/en-us/library/cc750889.aspx

http://www.excel-easy.com/examples/consolidate.html

Hope this is helpful.

Cheers

Thank you, FlameRetired. I used your last formula, worked fine except for when duplicated IDs with various values existed (AT5G01600.1 in D5 and D6 with values of 4.32 and 5.2 in attached example). In these cases, the mean (average) of values should be placed in front of a corresponding ID. With your formula, various values of a given ID were summed. Could you please modify the formula that consider the average (mean), not sum, of different values for a given ID?


Thanks a lot in advance

Thank you so much, it worked well. I approved it.