Seeking advice for complex charting algorithm (16K attachment)
 

(My apologies for the multi-post.)

At least it seems complex to me. I'm not exactly an Excel guru and maybe
this is actually something which can be done easily via Excel once you know
how. I'm hoping that Excel will have something built in to help me do what I
need to do.

I'm tracking a number of oustanding devleopment tasks at any given time. All
task move through the development pipeline in 3 phases. I get the data out
of SalesLogix into Excel in the following format: Each task occupies one row
in the sheet. Each row consists Task ID plus the dates on which the task
entered each Phase. In the workbook attached, this is modelled on the first
sheet, Raw Data.

What I need to do is to plot a chart, for any given date (or consolidate to
week or month) how many tasks were in Phase1, how many in Phase 2 and how
many in Phase 3. This chart will plot the periodic activity of tasks as they
move through the pipeline. The point here is that the chart should show
three trendlines which stay relatively stable relative to each other. If
this doen't happen, if I see a divergence in one or more Phases then I can
identify a bottleneck in a particular Phase and take steps to address it.

If I understand the approach I need to take, it seems to me that I need to
expand the matrix on the Raw Data sheet into a sequential listing of all
dates within the entire range, and then for each date maintain a count of
how many tasks were in each particular Phase on that particular date. I've
actually done this using VBA and you can review the results on the second
sheet named Expansion.

If I need to use VBA to do this, then I guess that's the way I'll have to
develop this. I'm wondering though, whether there is some built in
transformation capabilities in Excel which will do this on a range for me,
without requiring custom VBA?

Any and all suggestions will be most welcome!

Thanks!

Joseph Geretz