Multiple scatter categories in a single chart and trend line based on category averages

Hello everyone,

I've been working on this all day and I have finally run into an issue that I do not know how to resolve. What I need to do is is create a scatter chart with up to 6 categories- not all product IDs will have 6 categories- along the x axis (my sample data only has 3 categories, but I need 6, like the chart shows). I need 1 chart per product ID and then each of the segments in the product ID should populate a "Scatter Category Section" on the chart. And then I want to run a trend line, based on the average of each category, through the chart (one point for each category with a line through all the points). As an additional concern: I need the the categories to be subdivided into raw materials and labor. I am looking to do this with a macro because I have thousands of lines of data to do this with. I have a macro that automatically builds charts based on a similar, but different, data set. What the macro does is create a scatter chart for each product ID with B as the x axis and units of C and D as the Y axis--- I will post a sample of this data for your reference. The macro I am using is inside the attached workbook but I'll also post the text here. I have been unable to change the macro to chart the data in a reasonable way.

NOTE: the chart in my example is based on other data to illustrate the relative look I am looking for, I have not been able to get the macro to plot my own data similarly.

I apologize if any of this is unclear, please ask if this is the case.

Thank you, very much, in advance!


Sample Data3.xlsm

Please Login or Register  to view this content.