Stacked Column chart that groups dates by month

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Gidday,

I have the following list of data and am having trouble arranging it in a stacked column chart.
Column A = Completed date
Column B = Project name (just a letter)
Column C = Work units (the variable data).

--------------------------
Completed Project Work units
15/12/2010 A 30
20/12/2010 B 67
23/12/2010 C 92
14/02/2011 D 108
16/02/2011 E 50
18/02/2011 F 45
18/02/2011 G 29
4/04/2011 H 38
6/04/2011 I 8
6/05/2011 J 256
K 444
22/06/2011 L 242
M 21
N 16
O 29
P 146
Q 175
R 191
------------------------------

Chart specifications:
Y axis = "Work units" data.
X axis = time measured in months (one column per month).
Layers of each column = the various projects completed in that month.

Chart should not plot records with no "completed" date.
Chart should display months where no projects were completed.

Please let me know if the solution requires my data to be sorted in any particular way (I would ideally like a solution which doesn't require any sorting, but am open to anything).

So far I've been fluffing round with pivottables to try and arrange the data before charting... but I suck at pivottables so am getting nowhere. Not even sure if that's a necessary step.

Thanks for any help!

Straws
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Thanks Steve, but your solution does not fulfil the requirements.

The graph produced does not display months where no projects were completed.
The graph is plotting projects which have not been completed yet.
Also, the data has to be rearranged, which is not ideal.

Thanks for your time but I'm still looking for a solution here.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,184
I'd use a PivotTable and PivotChart, but you need to fill in your uncompleted dates with zeroes, so that you can group the date field.

Create a pivottable, with Completed in the Row label area, and Project as the Column labels, and Work_unitsin the Values data area.

Right-click a date in the pivottable, and choose Group from the shortcut menu, and choose Month and Year to group by, and enter a suitable Ending at: date, say 31/12/2011.

Then right-click any month, and choose Field Settings, and tick the Show Items with No Data checkbox ( in the Layout and Print tab ).

Don't panic when you see all the years from 1900 onwards ... filter the year, and click on Select All to deselect all, then scroll to select only 2010 and 2011.

Create your pivotchart.
 

Steve=True

Well-known Member
Joined
May 27, 2011
Messages
993
Sorry straws, didn't see those requirements.

You can simply insert cells from column F to Y for the months that are not showing and add them.

Also, i don't see that any non-completed projects have been plotted, for instance, M is not plotted, etc.

Finally, eventhough the data has to be rearranged, this can be done with formulas that look up the values, like index/match.

Good luck.
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hi,

I'm understand about the rearranging of the data via formulas and inserting cells to represent the months where no projects were finished. But all of this is very manual and I figure there must be a better solution which involves tampering with settings in the chart, or which would use a pivottable as part of the solution (also a vehicle for rearranging the data, but one that would be a more streamlined solution).

There are #N/A values plotted in your graph on the June column. I believe these are coming from the cells representing the projects which are not finished (no completion date).

There are also strange black squares at various points on the columns. I'm not sure where they're coming from.

In any case, thanks again for your help but I think a different solution is needed... if it exists.

Anyone up for the challenge??
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hey GlennUK,

No I didn't see your post there. Completely overlooked it sorry.

Well, that's exactly the kind of pivottable I wanted to come up with, so thank you for that. It's perfect.

I'm still not sure how to churn out a Pivotchart with it since I'm really just a newbie with the pivottable/chart side of excel.

Can you give me some pointers?

thanks

Straws
 

Forum statistics

Threads
1,081,693
Messages
5,360,658
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top