How to join/merge 2 worksheet tables from time-series in Excel?

Question

How do you join two datasets in excel from time-series, but the observations have been made at different times. How would you combine the observations in one table with the second?

Example:

table 1:

+---------+-------+
| date    | price |
+---------+-------+
| 1/14/96 | 1.95  |
+---------+-------+
| 1/19/96 | 1.97  |
+---------+-------+
| 1/22/96 | 2.01  |
+---------+-------+

table 2:

+---------+--------+
| date    | demand |
+---------+--------+
| 1/10/96 | 400    |
+---------+--------+
| 1/19/96 | 300    |
+---------+--------+
| 1/25/96 | 200    |
+---------+--------+

So that the desired result is either this:

table 3:

+---------+-------+--------+
| date    | price | demand |
+---------+-------+--------+
| 1/10/96 |       | 400    |
+---------+-------+--------+
| 1/14/96 | 1.95  |        |
+---------+-------+--------+
| 1/19/96 | 1.97  | 300    |
+---------+-------+--------+
| 1/22/96 | 2.01  |        |
+---------+-------+--------+
| 1/25/96 |       | 200    |
+---------+-------+--------+

Or this:

table 4:

+---------+-------+--------+
| date    | price | demand |
+---------+-------+--------+
| 1/19/96 | 1.97  | 300    |
+---------+-------+--------+

score 1 · Answer 1 · edited Sep 27 '18 at 14:32

There are several ways you could do this. How messy a job it is depends on whether or not you have duplicate dates in your table or common dates between them. My first thought is an INDEX/MATCH approach. Here's what I would do if I were trying to do it with your example tables:

Put table 1 in sheet 1, starting at cell A1, and table 2 in sheet 2, starting again at cell A1.
Create the column headers for table 3 in sheet 3.
Copy the dates from table 1 into table 3. Then copy the dates from table 2 to the bottom of the list you put in table 3. Now you have the dates from both in one column. Now, in table 3:
In the price column, use a formula along the lines of

=INDEX(Sheet1!$B$2:$B$4,MATCH(Sheet3!A2,Sheet1!$A$2:$A$4,0))

This pulls the price from the corresponding value in sheet 1.

In the demand column, use a formula along the lines of

=INDEX(Sheet2!$B$2:$B$4,MATCH(Sheet3!A2,Sheet2!$A$2:$A$4,0))

This pulls the demand from the corresponding value in sheet 2.

What you'll get is a table that looks like your Table 3, but with some duplicate rows. You can use the Data - Data Tools - Remove Duplicates feature to clean it up.

This may not be a perfect fit for your issue, but hopefully it's a starting point.