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

Posted on

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    |
+---------+-------+--------+
``````

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:

1. Put table 1 in sheet 1, starting at cell A1, and table 2 in sheet 2, starting again at cell A1.
2. Create the column headers for table 3 in sheet 3.
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:
4. 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.

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.

Thank you for all your help. I tried using querying and it work.

1. made a table query:

1. chose merge:

1. full outer join:

1. chose values:

1. got the desired result and then close and load:

1. result: