Single chart with two lines with different categories

[mbf82]

I have some data like this:

X1 | Y1
----------
1  | 100
2  | 120
3  | 130
5  | 160

X2 | Y2
----------
1  |  80
3  | 110
4  | 140
5  | 155

Basically the values (Y) are associated with a corresponding X value. The X values are not continuous and have some “holes”.

I need to put both on a single line chart, but, if I use the X1 values as categories, then the Y1 plot is correct, but the Y2 isn’t as the second value of Y2 (110) would end shown for X value 2, but its correct X value is 3.

I cannot change the format of the table, nor join the tables, unless I can do that automatically in a hidden sheet. The data needs to stay as it is.

To give more details on where I get the data from (in response to an answer I got), the data sets actually contain three different columns. A date, a number of days from that date (X), and the value (Y).

The X column is calculated with =DAYS(ValueFromTheDateColumn, DATE(InitialYear, InitialMonth, InitialDay)).

The initial date is different from the two data sets, so I cannot merge the two data sets into one as it would not make sense.

[Lupp]

What kind of chart?

[mbf82]

Lupp: a line chart, with points and lines style.

[Lupp]

(Sorry if I sound dogmatic.)
A ‘Line’-chart not being an ‘X-Y (Scatter)’ chart is a bad thing in itself. Just consider what the line should stand for. Anybody with at least a basic technical education should suppose the line to tell something like " there is a development in time" or “…regarding an abscissa-variable scaled in one of the usual ways”. ‘Labels’ aren’t. Use ‘Column’ charts in cases where there is no scaled “development” and where slopes don’t mean anything therefore.

[mbf82]

The Y value is a weight, so there’s a development over time.

[Lupp]

Quoting @mbf82: “The Y value is a weight, so there’s a development over time.”
If so you should use X-Y charting anyway. The only thing needed is that what you called ‘Label’ also is numeric: the dates presumably.
All the other problems will simply vanish.
See coming amendment to my answer.

[enka88]

You should join thoose table like this to get corrected result

X | Y1 | Y2

1 | 100 | 80

2 | 120 |

3 | 130 | 110

4 | | 140

5 | 160 | 155

[mbf82]

I cannot as (1) I cannot change the format; (2) it would not make sense due to what the data represents. I edited my original post to give some more details.

[Lupp]

Editing with regard to the recent comment by the OQ to his question:

Everything simple now. Just use the appopriate tool. ‘Line’ cahrts are of little use anyway. Use X-Y charting.
See this attached demo.

Orioginal answer:

Quoting @mbf82: “I cannot change the format of the table, nor join the tables, … The data needs to stay as it is.”

No way then. (I would tend to add “fortunately”.)
Anybody post here, please, if knowing better.

Another sceptical comment: If you cannot change the rotten tire, you shouldn’t drive the car.

Quoting @mbf82: “…unless I can do that {join tables} automatically in a hidden sheet. The data needs to stay as it is.”
This is feasible, but not exactly simple.

[mbf82]

I found a solution which is not perfect but good enough.

I added a new sheet for calculations only. The first cell (A1) is 1, A2 is =A1+1, A3 is =A2+1, etc.

The first cell in the second column (B1) is =VLOOKUP(A1,MainSheet.B:C,2,0), and so on. That is, it does a look up in Y1 of a value corresponding to the first column in the calculations sheet.
The third colum contains another VLOOKUP for the values from Y2.
I end up with a lot of “#N/A”, but that’s OK.