# Single chart with two lines with different categories

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.

edit retag close merge delete

What kind of chart?

( 2017-10-23 00:44:58 +0200 )edit

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

( 2017-10-23 09:33:10 +0200 )edit

(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.

( 2017-10-23 11:14:04 +0200 )edit

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

( 2017-10-23 21:54:04 +0200 )edit

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.

( 2017-10-23 22:53:53 +0200 )edit

Sort by » oldest newest most voted

You should join thoose table like this to get corrected result

## 5 | 160 | 155

more

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.

( 2017-10-23 09:32:04 +0200 )edit

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.

more

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.

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.

more