Ask Your Question

how to merge two tables with incomplete data [closed]

asked 2014-05-22 14:04:11 +0200

Steffen gravatar image


I'd like to merge to tables, which one of them has only a limit data set.

For example:

Table1: ID (1,2,3,4,5), name, address

Table2: ID (2,3,5), name, address, age

Is it possible to combine the data in one table. My attempts with vlookup and index-match are falling, because of the incomplete data set in table2. I tried it with Base and Calc, but without luck. If anyone has a solution either in Calc or Base I would really appreciate it.

thanks in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 12:08:10.525487

3 Answers

Sort by » oldest newest most voted

answered 2014-05-22 15:30:40 +0200

CEAuke gravatar image

I'm not sure I understand your example. If your columns and rows are alligned so that name and address are in the same column on both sheets, you can do the follwing:

Create a third sheet with all the columns. put the following formula and drag it where you need it. =IF(ISBLANK(Sheet2.A2),Sheet3.A2,Sheet2.A2)

This will always put the value from sheet2, unless it's blank in which case it will get the data from sheet3. You can probably add a bit more in it so that it wont show 0's where both sheets have a blank in a field.

edit flag offensive delete link more

answered 2014-05-22 17:05:27 +0200

Steffen gravatar image

updated 2014-05-22 17:06:19 +0200

Thanks for your answer CEAuke. Unfortunately it's not working. I'll try to give a better description.

For example:

Table1 contains 10 entries (from 1-10) with ID, name and address

Table2 contains only 5 entries (2,3,5,7,9) with the same data as in table 1 plus an extra column with age

In table2 the entire entries for 1,4,6,8,10 (including the ID) are missing. Now I want to merge the age column into table1. If I try to do this I get problems, because A2 in sheet1 differs from A2 in sheet2.

Hopefully this description is more precise.

is merging those two sets possible at all?

edit flag offensive delete link more

answered 2014-05-23 09:48:09 +0200

CEAuke gravatar image

Hi. Yes, it's called vlookup.

In the sheet with the most data (table1), you add a column for age. Then in that column you'll pull in the data from the other table with the vlookup function. =vlookup(table1idcolum, table2id:table2age,colcounter,0)

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-05-22 14:04:11 +0200

Seen: 3,807 times

Last updated: May 22 '14