Google-sheets – Combine two charts in one and auto-update them

google sheets

I have two tabs Sheet1 and Sheet2 with two columns each: Date and Temperature. I want a single chart of temperatures against date.

The problem is that the date columns are not necessarily identical (some dates are missing from some sheets). So, what I want to do is (full outer) join of the two tables on Date and then plot the two Temperature columns against the combined Date column.

The other problem is that new records are updated daily to the sheets and I want the chart to be updated automatically as it happens.

This can be relatively easily done in R or Python; however, it would be nice to do this in a more "visually interactive" environment.

Thus, my questions are: how to combine the two Temperature vs Date charts into one (taking care of the missing data) automatically so that no action is required when the sheets are updated?

Best Answer

You can use embedded arrays to join the two data sets for the purpose of plotting. For example, if your data is in columns A and B of two sheets, and has a header row, the following will join and sort the two sets: 

={Sheet1!A1:B1; sort({filter(Sheet1!A2:B, len(Sheet1!A2:A)); filter(Sheet2!A2:B, len(Sheet2!A2:A))}, 1, true)}

Explanation:

  • Sheet1!A1:B1 grabs the header row. The semicolon after it works as row separator in embedded arrays.
  • filter(Sheet1!A2:B, len(Sheet1!A2:A)) takes all A-B cells from Sheet1 with nonempty A entry (i.e., filters out blank rows)
  • filter(Sheet2!A2:B, len(Sheet2!A2:A)) does the same for Sheet2. Separated by ; these get stacked one over the other.
  • sort({...}, 1, true) sorts by the first column, ascending.

You can also combine non-consecutive columns in this way, like E and J. The embedded array would have the structure

{Sheet1!E,  Sheet1!J; Sheet2!E, Sheet2!J}

where commas separate columns and semicolon separates rows. Specifically.

={filter(Sheet1!E2:E, len(Sheet1!E2:E)), filter(Sheet1!J2:J, len(Sheet1!E2:E)); filter(Sheet2!E2:E, len(Sheet2!E2:E)), filter(Sheet2!J2:J, len(Sheet2!E2:E))}

(and then apply sort, as before).

Note that both columns must be filtered by the same criterion (whatever it is) to make sure their contents align.

Related Topic