Chart - Legend Formatting

[JBeaucaire]

This question embarrassingly demonstrated my lack of time spent in the "Charts" arena.

On the sample workbook is an X/Y Scatter plot chart. What I would like to do by hand or by VBA:

1) Reduce the legend to only unique values
* Data1
* Data2
* Data3
etc...

2) Have all the dots of the same data type in the chart be matching in color.

Data1 = red
Data2 = green
Data3 = yellow
etc...

Thanks in advance.

[shg]

I think you need to rearrange the date so that Data1 through Data12 are separate series. That would make each series name appear once, and all points within each series the same.

[JBeaucaire]

Ok, that very simple answer should be enough, and I don't even know what I don't know. Totally Chart clueless. Trying to avoid hours and hours of study to learn something I doubt I'll use again for another year or two after I finish this ask.

Hopeful...if it's any help, the names of the data1, data2, etc labels is a known quantity, so VBA could be told what values to look for and what color to apply...

[shg]

If you're clueless, I'm a scant 1/4 step ahead of you.

You might ask on MrE; Jon Peltier has been posting there frequently of late.

[JBeaucaire]

Cross-posted here with the workbook sample for convenience of the issue:
Chart - Legend Formatting

If this issue is resolved I will certainly and immediately update both threads with the solution and closure.

Thanks in advance.

[MrShorty]

What do you want to do where there are multiple entries for the same x value and the same data group? For example, B2 and B3 are both 6, corresponding x is 1 and the data group is data1.

Here's what I did to start:

A116 enter data1. continue down with each unique data group (I am expecting that you already have a utility that will extract unique entries from a list).
B116=SUMIFS(B$2:B$114,$A$2:$A$114,$A116) -- assuming you want to sum the values for each data group. This could just as easily be AVERAGE() or MAX() or MIN() or something more complicated.
copy B116 down and across. This gives a single row for each data group with the desired y values for each x value.
Use this range as the source for the chart. Row 1 can still be the x_values for each data series. Each row in the rows below 116 becomes its own data range.

I'm sure the same thing could be done in VBA, One way or another, the basic idea is like that -- figure out how to get the unique data groups in their own single row so that each data group has one data series.

[JBeaucaire]

Hmm, not sure this is what they want. The XY scatter plot is already the exact plot display they want, so I presume the duplicate values in the table simply plot over the top of each other. They don't want the display to change, only the legend trimmed down to unique values and the like values colored the same. My lack of familiarity with charting and charting VBA puts me at the "ask" table.

[xlnitwit]

Hi,

Perhaps something like the attached code- for some reason I can not post the code inline?

One might also use an array of specific colour values rather than the default workbook colours.

Sub formatChart()

    Dim dic As Object
    Dim n As Long
    Dim theChart As Chart
    Dim theSeries As Series
    
    Set theChart = ActiveSheet.ChartObjects(1).Chart
    Set dic = CreateObject("Scripting.Dictionary")
    
    With theChart
        For n = .SeriesCollection.Count To 1 Step -1
            Set theSeries = .SeriesCollection(n)
            If dic.exists(theSeries.Name) Then
                .Legend.LegendEntries(n).Delete
            Else
                dic(theSeries.Name) = ActiveWorkbook.Colors(dic.Count + 1)
            End If
            With theSeries.Format
                With .Fill
                    .BackColor.RGB = dic(theSeries.Name)
                    .ForeColor.RGB = dic(theSeries.Name)
                End With
                With .Line
                    .BackColor.RGB = dic(theSeries.Name)
                    .ForeColor.RGB = dic(theSeries.Name)
                End With
            End With
        Next n
    End With
    Set dic = Nothing
End Sub

[shg]

Perhaps something like the attached code

Nice work!

[JBeaucaire]

Yes, yes, yes! That did both things quite neatly. And I can tweak this from there to give them the code-coloring they want. Thanks so much.

[xlnitwit]

Thank you both for the rep points and, perhaps more importantly, praise from MVPs! A fine day for me!