3

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • Does this requite that the columns are adjacent?
    – sds
    Nov 17, 2015 at 15:07
  • What if my columns are E and J instead of A and B? Thanks!
    – sds
    Nov 17, 2015 at 15:12
  • Added this to the answer.
    – user79865
    Nov 17, 2015 at 16:58
  • thanks - do I need to make sure that the date columns are identical and increasing myself? I.e., what I want is full outer join (in the SQL sense)-is it what your code will somehow accomplish?
    – sds
    Nov 17, 2015 at 17:05
  • I left out sort and headers from the non-consecutive version because they are the same as before, and the formula is long enough already... You can combine based on another criteria by changing the second parameter of filter: e.g. filtering with len(Sheet1!E2:E)+len(Sheet1!J2:J) picks the rows where at least one of two columns is nonempty. If you want to do more SQL-like stuff, see query.
    – user79865
    Nov 17, 2015 at 17:10

Your Answer

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

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