Scatter plot where reoccuring labels are part of the same line

[runeks]

I have a hard time explaining this in a single line, so here’s a better explanation (hopefully)

I have some data that looks as follows:

		Time	Altitude
John	10		10
Lisa	10		5
Mona	10		0
John	20		20
Lisa	20		25
Mona	20		40
John	25		40
Lisa	25		55
Mona	25		80

This data series displays the altitude of three people at various times. For example, you can see that John was at an altitude of 10 at time 10, and at an altitude of 40 at time 25. So the label (first column) appears many times with different data points (second,third column) for this label (person in this case).

I want to create a chart from this that looks like this, without having to reformat the data:

So for each row I add to the data table, I specify another (time,altitude) pair for the person name in the first column of the row.

Is this possible?

[Lupp]

If the structuire as suggested by the given example (fix number of people, recurrence of names in unchanged order and without missing entries and identical time values in every group) is assured a rearrangement by formulae as demonstrated in the attached example will do the job.

[begin EDIT]
The example got a significant enhancement. I attach the new version.
ask36083ChartExperiments002.ods
[end EDIT]

There may be also solutions using ‘Pivot Table’ or something. Others may stress that.

[oweng]

@Lupp, I have deleted my original comment and provided an alternate (simpler) example. I still feel that your solution is the better one as arrangement of data prior to charting is generally preferable i.e., the cell ranges to create your chart example are much easier to make and maintain.

[mariosv]

Maybe Pivot Table another way to rearrange data.

[oweng]

Here (ODS) is a simpler, alternate version that uses manually input data range values e.g., for John

X-Values

$Sheet1.$B$2,$Sheet1.$B$5,$Sheet1.$B$8

Y-Values

$Sheet1.$C$2,$Sheet1.$C$5,$Sheet1.$C$8

Note that in the data arrangement in the question, after defining the cell ranges for all the data series, Calc will treat the data as being row-based, even though in terms of the XY (Scatter) chart type the data is column-based i.e., the X and Y values are in columns.