Merging data with a similar value in Excel

New Member
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.

Cheers

Attachments

• Untitled.jpg
30.9 KB · Views: 3

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mart37

Well-known Member
Use a pivot table or use: average.if()

sandy666

Banned - Rules violations
 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

Power Query:
``````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

New Member
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

Banned - Rules violations

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
----
for Pivot Table
from the ribbon - Insert - Pivot table
X into the ROWS
Y into the VALUES then select from Value Field Setting - Average

sandy666

Banned - Rules violations
for the last line of the previous post

New Member
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

Banned - Rules violations
You are welcome and thanks for the feedback
Have a nice day

Replies
4
Views
97
Replies
3
Views
188
Replies
3
Views
181
Replies
13
Views
202
Replies
6
Views
143

1,126,893
Messages
5,621,486
Members
415,844
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back