Thread: How can I create this chart?

Hi all!

A need to create a chart, and i don't even know what to call the type of chart i need, and i definitely don't know how to create it. I am using Excel 2007 and have no big problem programming in VBA, even if I haven't worked so much in Excel.

My data has 2 dimensions: date (x), dataset (y) and a value (hmm, which is actually 3 dimensions).

The chart should have three dimensions with dates on the x-axis and dataset on the y-axis. There should be a horisontal line for each dataset and on this line there should be symbols with their background color and lables dependent on the values in the datasets. A value of 0 should be ignored (no symbol). Colors for the symbols should come from some sort of lookup table.

The chart will have 50 to 200 dates, about 40 datasets, about 1000 values that aren't 0 (= 1000 symbols), and values in the datasets will go no higher than 20.

A small subset of a typical dataset might look like this:
Dataset.jpg

And the chart generated by that should look something like this:
Chart ask forum.jpg

I did experiment with a small bubblechart with separate data columns and sets in the chart for each color. But i am not sure i liked the result. And all those data columns would probabaly make the chart update really slow with a full dataset.

So, how do i create this type och chart?

It is more helpful to upload a sample workbook so that those helping can view the data in context and not have to recreate it when developing a solution.

Maybe you can adapt this approach from Andy Pope: Tile Chart