Transform table with duplicates into table without duplicates and corresponding averages

[u0046619]

Hi all!

I guess my question is easier to understand when looking at the attached Excel file.

The easiest way to formulate my question is: how to get from the table on the left to the table on the right using Excel formulas and functions? ;-)
But more in detail...

- I have 4 features (column E) which all have a different data point (column D). I would like to make the average of the differences for all the features separately (column J), but as you notice, the number of datapoints is different for each feature.

- I would also like to mention in 2 separate columns the start (column H) and end (column I) corresponding to the first and last position (column C) of each feature.

Is it possible to do this in Excel in a more automated way since I have 1000s of these features...?

It's a complicated question, I am sorry, but every bit of information is highly appreciated!

[Pete_UK]

You can use this formula in J2:

Please Login or Register  to view this content.

then copy down to J5.

Hope this helps.

Pete

[ChemistB]

A pivot table also will work (if the position values always move from low to high)

In the row fields, Chromasome and Feature
In Data Minimum of Position, Maximum of Position and Average of Difference

Format the Row fields to tabular. See example

[Pete_UK]

The attached example generates the summary table automatically. Let me know if you need an explanation of the formulae (dinner's ready now).

Hope this helps.

Pete

[u0046619]

I will have a look at it tomorrow, diner is also ready now ;-)
But I would like to thank you already for your help!
I'll let you know as soon as I had a closer look.
Thx!

[u0046619]

Hi Pete_UK and ChemistB!
Thanks a lot both of you for your solution of the problem; it really helped me a lot!
I just generated my first Pivot Table ever, great! :-)

[ChemistB]

Glad to help. Thanks for the star click.