Merging data with a similar value in Excel

[esasad]

Hello everyone,

I have sets of data points (X and Y shown in blocks with a similar color), each of the data sets with a unique X value. I would like to merge the similar (X) values and only have one value instead in each colored block as the X value. In each data set (colored blocks), I would like to have an average of the Y values in a separate box.

I just manually did the calculations for some blocks, but due to a high number of data sets, I am just wondering if you could kindly propose to me a formula/code that I can use. Any hint would be also highly appreciated.

I would appreciate your help in advance:

Cheers

 

[mart37]

Use a pivot table or use: average.if()

 

[sandy666]

x	y		x	y
2	10		2	14
2	15		3	40
2	17		4	82
3	20
3	30
3	50
3	60
4	65
4	75
4	80
4	90
4	100

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"x"}, {{"y", each List.Average([y]), type number}})
in
    Group

or

x	Average of y
2	14
3	40
4	82

 

[esasad]

Dear Sandy666,
Thank you so much for your work, it is exactly what I have been looking for recently. I have tried to spend some time to understand how I should implement it in Excel through watching some YouTube instructions, but I would like to ask you kindly let me know the procedure in Excel to run this code.

Cheers,

 

[sandy666]

make your range as Excel Table (Ctrl+T)
select any cell in this table
from the ribbon Data tab choose From Table
it will open Power Query Editor where:
- select column X
from PQE ribbon Home tab choose Group By
setup for this you've here

then OK
Home tab - Close&Load
----
for Pivot Table
select your range
from the ribbon - Insert - Pivot table
X into the ROWS
Y into the VALUES then select from Value Field Setting - Average

 

[sandy666]

for the last line of the previous post

 

[esasad]

Hi Sandy666,
I would like to thank you so much as your suggestions worked very well. With your help, I have managed to plot all my graphs in a nice way.
Cheers

 

[sandy666]

You are welcome and thanks for the feedback
Have a nice day