Finding data and plotting a scatter chart with VBA

Hi,

Using the dataset you provided as an example, you could define named ranges for each of the groups' x and y values like so:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Then plot the named ranges in the chart,

X Values = Sheet1!aaa_x
Y Values = Sheet1!aaa_y

I doubt if scatterplot of 1000 series (a) is anything more than just noise, (b) possible in excel - AFAIR 255 is series number limit.

Provided your data is representative (in a sense that for given series/group there is no x duplicates) you can first make a Pivot table out of your data (groups in row headings, x's in column, or vice versa and Y's in data, then copy/paste as values (pivot table cannot be a source for scatterplot). And now you have your data ready for scatterplot. Of course this can be written as VBA procedure (if you plan to make multiple scatterplots from many different sets of data). but please check my above (b) doubt.

How about your sample: is representative (in a sense that for given series/group there is no x duplicates)

How would you feel about a set of helper columns to the side of your main data table? I would probably do something like this: https://peltiertech.com/conditional-...-excel-charts/ which uses a series of helper columns with a simple IF() function to "conditionally format" the different group types. The most difficult part, if it must be automated, is creating the list of unique group names across the top by formula. If this step does not need to be automated, then it is as easy as entering the group names across the top row. Steps for this approach (if needed):
1) Enter group names across top row (starting in D1).
2) Enter IF() formula in D2. =IF(D$1=$A2,$C2,na()) note the mix of relative and absolute references to make copying easy, then copy down and across.
3a) Select column B and columns D to whatever.
3b) Insert scatter chart
3c) Format as desired

Alternatively, I could see using a pivot table for this. As Kaper says, you cannot use a pivot chart, so you have to pay attention to how it is done.
1) Create pivot table. If you are unfamiliar with pivot tables, start here: http://www.excel-easy.com/data-analy...ot-tables.html
1a) x axis as row labels
1b) group as column labels
1c) sum of y axis as value field. Note at this point that this entire approach will likely fail if it is possible to have two entries for a given group and x axis value. You would need to know before trying this if this is a possibility.
2) Insert scatter chart. Note that, because a pivot chart cannot be a scatter chart, and a chart based on a pivot table will want to convert itself to a pivot chart, you need to pay careful attention to how you create the chart. I recommend this discussion here: https://peltiertech.com/regular-char...-pivot-tables/ Follow the instructions carefully. It will be a bit tedious to create the first time, but, once it is created, updating should be as simple as editing the raw data and refreshing the pivot table.

Thanks, I will try to do this you way

I ment:
is it possible that for given group there will be data like:
aaa 2 5
aaa 3 2
aaa 3 5
aaa 4 4

But now try to deal with MrShorty's answer - it also is based on assumption of unique x in given group (see 1c in post #8)