"joining tables"

A

anarchitect

hi guys

im looking to effectively create a join function similar to access fo
a work project. the reason why im not doing it in access is because w
dont have licences for that part of office.

i have to spreadsheets, with 1 corresponding column between the two.
need to create a new spreadsheet joining the two spreadsheets on tha
corresponding column. i hope the example below makes sense....:

-| A |--| B |--| C |- ====== -| D |--| B |--| E |--| F |-
===================================
-| a |--| 1 |--| e |- ====== -| x |--| 2 |--| i |--| s |-
-| p |--| 2 |--| d |- ====== -| g |--| 3 |--| f |--| e |-
-| r |--| 3 |--| 4 |- ====== -| q |--| 1 |--| t |--| w |-

joining these two would create the following:

-| A |--| B |--| C |--| D |--| E |--| F |-
=============================
-| a |--| 1 |--| e |--| q |--| t |--| w |-
-| p |--| 2 |--| d |--| x |--| i |--| s |-
-| r |--| 3 |--| 4 |--| g |--| f |--| e |-

i need to be able to carry this out on spreadsheets that are 1000's o
rows. any help would be most appreicated. :
 
F

Frank Kabel

Hi
looking at your example it seems you're using column B for
matching both sheets together. If yes try the following
formulas on your first sheet:
D1:
=INDEX('sheet2'!$D$1:$D$1000,MATCH(B1,'sheet2'!
$B$1:$B$1000,0))

E1:
=INDEX('sheet2'!$E$1:$E$1000,MATCH(B1,'sheet2'!
$B$1:$B$1000,0))

F1:
=INDEX('sheet2'!$F$1:$F$1000,MATCH(B1,'sheet2'!
$B$1:$B$1000,0))

Copy these formulas down for all rows
 
O

onedaywhen

anarchitect said:
im looking to effectively create a join function similar to access for
a work project. the reason why im not doing it in access is because we
dont have licences for that part of office.
Click to expand...

As a fellow anarchist ('Fight the power!') I'm duty bound to inform
you that you don't need the MS Access application to create and/or
query a Jet database (.mdb file). You can do all this on the fly using
only Excel and ADOX (to create the .mdb file) and ADO (use DDL
statements to create the schema e.g. CREATE TABLE etc and SQL for
queries).

You do need MDAC, free MS download and shipped with Excel (Windows
2000 installs includes MDAC 2.5, Windows 98 SE installs MDAC 2.1 by
default, Windows ME installs MDAC 2.5 by default, etc), and Jet, free
MS download (still?) and shipped with early versions of MDAC. So it's
highly likely you and your colleagues/users already have the necessary
components, legitimately too, which is bad news for anarchists :-(

--
 
Top