Extract, Sort and Graph Table

[atwy]

Hi,

Given a long table with various columns, how can I extract the rows based on defined column data, paste the selected into another worksheet, then sort and graph it as a scatterplot?

For Eg. with the attached spreadsheet with a list of fruits, how can the fruits that are green (watermelon and pear) be extracted, then sorted by price, and then graphed as a scatterplot (size vs price)?

Thanks in advance!

[pbengtss]

I guess you could make a pivot table/graph as attached.

[Kaper]

As you do not need sorted data for scatterplot (especially when plotted with no inteconnecting lines):
Just plot of the filtered data would do. Remember to set in properties to not change size and not move with cells.

[atwy]

Thanks for the suggestions guys!

Is there a way to automate this using VBA code? Such that with a table of data, how can Excel automatically filter and output such scatterplots based on pre-defined criteria?

The data in the table will be refreshed periodically.

Thanks!

[Kaper]

have a look on such sample macro:

Please Login or Register  to view this content.

[atwy]

Hi again Kaper, that's a wonderful piece of code. I've tried to use it but it gives a "application-defined or object-defined error". This occurs at the line "ActiveSheet.ChartObjects(1).Activate"

[Kaper]

Try 3 measures:
- add

Please Login or Register  to view this content.

before

Please Login or Register  to view this content.

- move your main chart slightly up to have upper left corner above the row with filter header.
- make sure all old pictures are deleted from the sheet before you start to create new. you may add such code to do so:

Please Login or Register  to view this content.

[atwy]

Hi Kaper realise that a chart needs to be already created in order for this to work. It works now after I've added a chart.

It looks nice but unfortunately the data points aren't viewable as they are pasted as images. Is there a suggestion on how to create individual tables and their own charts?

Thanks again!