The following is just to give you an idea on how to get started, not to write the code for you.
As it currently stands, your data can't be plotted the way you wish without some "manipulation". The way I see it, you have two options:
- Transform your data in another workbook/worksheet.
- Use a pivot table to insert your data and plot it from there.
Which one to choose largely depends on the size of your data and also in your knowledge of Excel and/or VBA. For example, since I don't know anything about pivot tables apart from the theory around them, I'd personally go with option 1, but it's ultimately up to you to decide what's best for you.
Let's simplify your data slightly. Where Dx is an incremental date, Wy is a day of the week (1-7) and Sz is a sales figure (numbers here don't mean anything except to differentiate them), your data currently looks something like this:
D1, W2, S1
D2, W3, S2
D3, W4, S3
D4, W5, S4
D5, W6, S5
D6, W7, S6
D7, W1, S7
D8, W2, S8
D9, W3, S9
D10, W4, S10
D11, W5, S11
D12, W6, S12
etc.
Since you only care about days of the week and sales figures for those days according to your post, we can forget about the dates. You then want to transform your data to look like this:
W1, S7, ..., ..., ..., etc.
W2, S1, S8, ..., ..., etc.
W3, S2, S9, ..., ..., etc.
W4, S3, S10, ..., ..., etc.
W5, S4, S11, ..., ..., etc.
W6, S5, S12, ..., ..., etc.
W7, S6, ..., ..., ..., etc.
Basically, you will have 7 rows, one for each day of the week. Each row will have a number of columns to represent all the sales figures that are associated with that day. Note that, if you so choose, you can put the days of the week as columns and expand your sales figure downwards in rows. You can decide what's best according to the size of your data. Once you get your data in this format, adding a chart to plot the data will be a trivial task.
Of course, the main problem is the transformation of your data. I can't help you with that part. However, once you make a start on the code and get stuck somewhere, I'm sure the SO community will be happy to help you with any specific issue you may face.
