Gantt Graph - Grouping Y-Axis Values

**AnotherNewbie** · 03-06-2015, 03:51 PM

I successfully created a Gantt Graph for my data and I'm getting pretty closed of getting what I want. My problem is, I have 3 groups and each group has dozen of values. When I create the graph, I get all single values on the Y-Axis (all records), but I only want to show the 3 groups, so the lines won't be cascading lines, but a straight lines for each group. I've been looking and looking online, but I can't find anything. I know about http://peltiertech.com, but there are not many details on how he accomplishes some of his things.

Here's a screenshot of my table/graph. Basically, I need one line for B, D and E on the Y-Axis.

screenshot.JPG

**Kaper** · 03-07-2015, 07:36 AM

probably well designed scatterplot could do (Y axis markers with "artificial axis" and series points labels), but:

1) to experiment one would need some data (so workboook as attachment would be definetly better than screenshot)
2) to understand what you really want to achieve, a simple schema (for instance drawn in paint) would be better than seeing what you already have and description how it shall be changed

**AnotherNewbie** · 03-09-2015, 07:46 AM

Thank you for the reply.

Here's my spreadsheet:
Attachment 381942

I want all 'A' line segments to show under one 'A' row and the same with the others. If I have 12 entries for 'A', I don’t want 12 individual entries in the Y-Axis, I just want one

Capture2.JPG

**Kaper** · 03-09-2015, 11:54 AM

Something went wrong with attaching file.
Trying to download the message is:

Invalid Attachment specified. If you followed a valid link, please notify the administrator

**AnotherNewbie** · 03-09-2015, 12:30 PM

Sorry, I'm not sure how the attachments work on this site. I put it in my server:

http://headbangerstudio.com/Others/ChartTest.xlsx

D

**Kaper** · 03-11-2015, 09:03 AM

To obtain desired result you have to rearrange your data.
Propoer sentence is seriesname, startvalue, lengthofperiod1, lengthofbreak1, lengthofperiod2, lengthofbreak2, ... etc.

You can do it either manually, or with formulas (rather complicated if data is similar to this shown in screenshot from March 6th) or with a procedure in VBA
I used the last approach. bit any resulting in sequence of name, start and then lengths of periods and lengths of breaks would do.

see attachment. I added there also part for chart drawning and some formatting, because will probably return (refer others) to this thread.

the code is (adjust if your data does not start in A2 and you do not want output of values in O2 (and down/right):

Please Login or Register  to view this content.

**AnotherNewbie** · 03-11-2015, 10:33 AM

Thanks so much for doing all of this! But, I can't believe there isn't a simpler way of doing this, without using a VBA Script. I think the 'graph wizard' should have a section where Axis values can be grouped based on a common variable. It wouldn't take much to add this option in there. Maybe in Excel 2020..hehe!