activity plan gant chart

[ids_73]

hi there to all,

the last few day i am trying to solve a problem with a chart.

here is the story:

i have a sheet with the following layout:

customer name activity type start date duration
cust1 act1 01/01/17 5
cust2 act2 04/01/17 7
cust3 act3 03/01/17 5
cust1 act4 03/01/17 4
cust4 act5 14/01/17 5
cust1 act6 14/01/17 15

what i have to do is to represent the above data in a chart showing for each customer how many activities has and what is the duration of each one.

any idea will be appreciated.

thanks in advance.

[jeffreybrown]

Have you looked into a pivot table?

[ids_73]

dear Jeff

Thanks for the reply.

i did try the use of pivot table but that what i manage to get is for each customer deferment lines of each activity.
what i am looking for is a single line for each customer but with a split in the period for every activity.

[jeffreybrown]

It would help tremendously if you mock up a workbook with what you have and what your desire?

It often helps to see your vision as words sometimes don't draw the accurate picture...

[ids_73]

dear Jeff

this is what i am looking for:
activity graph.png

and this is what i have manage to get:
current file.png

[jeffreybrown]

Not sure I know how to make this work so I've asked others if they can help.

Have you looked for Gantt Chart templetas such as @ https://www.vertex42.com/

[ids_73]

i did but nothing to help me.

if you come up with any idea let me know !!!

kindly
IDS

[Richard Buttrey]

As Jeff said it would assist if you would upload your raw data workbook.

Does the Gantt chart have to be exactly like that with different activities on the same row?
Most of the Excel produced Gantt charts I've seen have each activity on a row of its own, and in which case the solution is fairly straightforward - and indeed there are dozens of templates out there on the interweb

With your proposed layout how would you handle overlapping activities for the same customer?

[southward]

Hi,

Here is a website the shows how to use Pivot Table to create a gantt chart.

http://www.excel-exercise.com/charts/gantt-chart/

Attached is a simple gantt chart I created.

Hope this is helpful.

Cheers

[ids_73]

hi there to all,

thanks for the info southward. i have seen that before.

attached i have a fraction of the data that i will use.
that data represent the marketing activities ans discount promotion in supermarkets during the year base on different products.

base on the Rechard's comment regarding the case of the overlap of an activity with another this is something that i didn't think of

[Richard Buttrey]

Does the attached help

[ids_73]

dear Richard,

thanks a lot!!
that helps a lot too!!!
can it be dynamic?

either way that solves my initial quest

[Richard Buttrey]

..or here's another approach using conditional formatting

[PietBom]

I made a start with a different approach.
This gives an impression of how it will look like.
I only have to make a macro (VBA code) to convert the customer/activity table to the customer/phase matrix.

[PietBom]

Now with a bit of VBA code.
It is not fully dynamic, but that can be made too.

[ids_73]

dear PietBom

thank you for the post.
this approach is even closer to that i am looking for.

but why it has max clients and max phases?

best regards

[PietBom]

The graph has 2 series (gap + duration) for each activity per customer (shown in columns).
The macro can gererate more phases, but the chart as it is now will not show more than 9 activities per customer.
Also the ranges for the number of customers is now set to 11, so if you have more customers those are not shown on the chart (height).
That is what I meant to make the size of the chart dynamic.
But if you have not so many customers and not many activities per customer, my example will do the job.

[Maddart]

here is a Gantt chart I made after some help from "", sorry I do not remember name... Be he gets all the credit