0

In Google Sheets I have two timeline charts with source data Sheet1!A1:B100 and Sheet2!A1:B100. Dates in Sheet1!A and Sheet2!A mostly do not overlap - Sheet1!A are more or less random (datapoints are collected once every 1-2 weeks) and dates in Sheet2!A are 1st day of every month (datapoints are autogenerated once a month).

If I just add data range Sheet2!A1:B100 to timeline chart plotting data range Sheet1!A1:B100, I get error "Each values column may be followed by one or more annotation columns. column number 2 is of type date".

Is there a way to merge two timeline charts with different date-sets?

edit: here is a sheet with the problem: https://docs.google.com/spreadsheets/d/1TGoHPodslxw01PVf8Yp4SI15saedY8iID3FegJg6lmg/edit#gid=1772915307

I would like to merge those two graphs. I'd like to have those two lines on the same graph.

3
  • not very well explained enough to begin to formulate a response. Create an anonymous sheet using Blank Sheet Maker which outlines your issue and edit it in to your question. Jun 5, 2023 at 17:22
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Jun 6, 2023 at 2:15
  • @graham please find the example sheet added.
    – dijxtra
    Jun 9, 2023 at 13:10

1 Answer 1

1

To merge the two charts, you will have to merge the data. One easy way is to look up the values in the sparser table by the keys in the denser table, and place the lookup results next to the values of the denser table.

In your sample spreadsheet, put this formula in cell C1:

=arrayformula( iferror( vlookup(A1:A, sort(D1:E), columns(D1:E), true) ) )

Then select columns A:C and choose Insert > Chart.

2
  • Fabulous, answers my question exactly. Is it now possible to have those two series on different vertical axes? One timeseries on left vertical axis, one timeseries on right vertical axis?
    – dijxtra
    Jun 14, 2023 at 13:41
  • Please ask only one question per post. Post a new question instead. Jun 14, 2023 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.