Hello,
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.
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.
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?
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)