This probably has a very simple solution, I just don't know what search terms to use.
I have several different Excel workbooks. They all have a sheet with the same information, some of which is the same, and some of which is only non-null in one of the workbooks. Something like this:
Workbook 1:
| ID | Name | Value |
|---|---|---|
| 1 | Foo | |
| 2 | Bar | 2 |
| 3 | Baz |
Workbook 2:
| ID | Name | Value |
|---|---|---|
| 1 | Foo | 4 |
| 2 | Bar | |
| 3 | Baz | 9 |
Just with a lot more workbooks. However, the "Value" field is only populated in one workbook for each row. In all the rest of the workbooks, it's null.
Now I need to merge them into this:
| ID | Name | Value |
|---|---|---|
| 1 | Foo | 4 |
| 2 | Bar | 2 |
| 3 | Baz | 9 |
I have created data queries with each separate workbook as a source, based on the ID field, but when I merge the queries, it only takes the values from the first workbook. The rest are null.
How do I merge the "Value" columns, by keeping all the non-null values from all workbooks/queries?
