Time graph of non-numeric data

Question

I'm trying to draw a scatter-plot in Excel 2007, with times along the x-axis, and text values along the y-axis. Since I can build numeric values for the y-axis, another possible way to look at this would be to change the labels along the y-axis to be text (instead of their default numeric values).

My actual dataset looks something like this:

start, end, flag, value
1:00am, 2:30am, 0, A
2:30am, 7:00am, 3, D
7:00am, 12:30pm, 2, C
12:30pm, 4:00pm, 7, H

Generally, if I were to plot this, I would translate it to something like:

time, flag, value
1:00am, 0, A
2:30am, 0, A
2:30am, 3, D
7:00am, 3, D
7:00am, 2, C
12:30pm, 2, C
12:30pm, 7, H
4:00pm, 7, H

Then I would build a scatter-plot with the time column as the x-axis and the flag column as the y-axis. However, I'd much rather have the value column provide the label on the y-axis.

I can't seem to find a way to do this in Excel.

Am I missing something or is there another tool I should look into?

Answer 1

This is about a close I can manage, with heavy reliance on Jon Peltier, of course:

I doubt it is good enough so I am not cluttering this 'answer' up with much detail. Even this poor offering was extremely fiddly so if an acceptable quality was even more so I think it likely most people would not consider that worth the effort.

The chart is a combination of a scatter plot that is visible and a bar chart whose axis only is visible, and the data was first sorted into alphabetical order. The data is paired, hence duplication of the letters) and a Gantt-type bar chart (showing duration rather than just start and finish) would make adding text labels for the vertical axis very much easier and neater.