joining two tables

J

Jan Mannoury

i have two tables with one column in common. is there a way that i can
join them?

regards,
janm
 
P

Pete_UK

Yes, and you can get advice on how to do it if you provide a few more
details.

For example, how many columns and rows of data in the two tables, are
the two tables on separate sheets, are these separate workbooks, do you
have any duplicate entries in the common column, do you want unique
entries in the combined table, how do you want to treat data which
appears in both tables etc etc.

Pete
 
J

Jan Mannoury

Yes, and you can get advice on how to do it if you provide a few more
details.

For example, how many columns and rows of data in the two tables, are
the two tables on separate sheets, are these separate workbooks, do you
have any duplicate entries in the common column, do you want unique
entries in the combined table, how do you want to treat data which
appears in both tables etc etc.
Click to expand...

i have 2 spreadsheets.(workbooks?) both one table. i could copy the
tables into one file i guess.
the tables are like this:

table1:
nr text
------------
1 yes
2 no
4 maybe
8 shure
9 thing

table2:
nr text2
------------
1 ja
2 nein
3 njet
8 hello

i need a result like this:
nr text text2
---------------------
1 yes ja
2 no nein
4 maybe
8 shure hello
9 thing

so table1 is the prime table. if exists i want to add information in a
text2 field. there is information in table2 that i don't need

in sql i think it would be something like:
select * from table1 left join table2 on table1.nr = table2.nr

janm
 
P

Pete_UK

Ok, starting with your main table in Sheet1, copy the secondary table
into Sheet2 of the same workbook . Assume you have 100 rows in Sheet1
with a header in row 1, and 50 rows in Sheet2 also with a header in row
1. If your data is only two columns wide (A and B), enter this formula
into cell C2 of Sheet1:

=IF(ISNA(VLOOKUP(A2,Sheet2!A$2:B$50,2,0)),"",VLOOKUP(A2,Sheet2!A$2:B$50,2,0))

Copy the formula down for as many rows as you have data in Sheet1 (i.e.
100). You can then fix the values by highlighting column C, clicking
<copy> then Edit | Paste Special | Values (check) OK then <Esc>. You
can then delete Sheet 2 from the workbook and use File | Save As to
save the workbook with a different name.

Hope this helps.

Pete
 
J

Jan Mannoury

Ok, starting with your main table in Sheet1, copy the secondary table
into Sheet2 of the same workbook . Assume you have 100 rows in Sheet1
with a header in row 1, and 50 rows in Sheet2 also with a header in row
1. If your data is only two columns wide (A and B), enter this formula
into cell C2 of Sheet1:

=IF(ISNA(VLOOKUP(A2,Sheet2!A$2:B$50,2,0)),"",VLOOKUP(A2,Sheet2!A$2:B$50,2,0))
Click to expand...

yes!
it took me a little time because someone in his infinite wisdom
translated function names and even some syntax to dutch in my excel
but this:
=ALS(ISNB(VERT.ZOEKEN(F5;Blad2!A2:B1510;2));"";VERT.ZOEKEN(F5;Blad2!A2:B1510;2))
did it.

thank you very much.
janm
 
Top