Merging data with a similar value in Excel

esasad

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    30.9 KB · Views: 3

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
Use a pivot table or use: average.if()
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
xyxy
210214
215340
217482
320
330
350
360
465
475
480
490
4100

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"x"}, {{"y", each List.Average([y]), type number}})
in
    Group
or
xAverage of y
214
340
482
 

esasad

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
grpby.png

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
 
Solution

esasad

New Member
Joined
Jan 15, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,499
You are welcome and thanks for the feedback
Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,459
Members
415,971
Latest member
Graeme M

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top