Join Two Spreadsheets on a Common Column in Excel or Openoffice

Join two spreadsheets on a common column in Excel or OpenOffice

In Excel, vlookup can do part of what you're asking. Specifically, you can use vlookup to do a left or right outer join, but not a full outer join (like your table result).

To do an outer join for your example above, add the following to the C2 of "Table B" (or copy "Table B" and then do this):

=vlookup(
    a2, # the cell value from the current table to look up in the other table
    table_a!$1:$174832718, # the other table
                           # don't manually type this--select the entire 
                           # other table while the cursor is editing this
                           # cell, then add the "$"s--Excel doesn't
                           # automatically add them
                           # (the syntax here is for different sheets in
                           # the same file, but Excel will fill this in 
                           # correctly for different files as well)
    2, # the column to get from the other table (A=1, B=2, etc.)
    FALSE) # FALSE=only get exact matches TRUE=find approx. matches if no exact match

You should then be able to expand it to deal with multiple rows and multiple imported columns.

Merge two csv files based on common column

My approach would be to use VLOOKUP on the P/N in file one and grab the data from file two.

VLOOKUP(p/n,filetwotable,2,0)

drag this down to the bottom of column D, modify the third parameter (2) for the next two columns in columns E and F.

I would copy/paste special values to kill the formulas.

I do this often; I create a template then drop the data into it.

Good luck!

merge 2 calc documents based on common field

You could try to use the VLOOKUP function to search the first and last names for each entry, but i don't think that this would be a good solution. Instead, i would recommend to use the database features of OpenOffice:

1. create a OpenOffice.org Base database,
2. import both CSV files into separate tables and
3. create a query to select all the mail adresses from table 2, joining the name fields from table 1 based on the email address.

Step 3 requires some SQL knowledge, but it's possible to build such a INNER JOIN Query using the design view, too (without knowing much SQL). You will need to do the following (in ooo.base, after importing both csv files):

1. create a new query in design view (using Menu Insert -> Query (Design View)..., or using the Create Query in Design View... Task);
2. add both tables to the query; the resulting designer window should look as follows:
   

3. define the relationship: drag the mail column from table 2 to table 1 (opposite direction should work, too); display of result in query designer:

   

4. in the Fields table (lower half of the design view), click into the Field cell in the first column, select e-mail field from table 2 (csv 2);
   

5. in the second and third column, select first and last name columns from table 1 (csv 1). The resulting fields table should look as follows:
   

Running this query should give you a table of all the mail addresses from csv 2 with corresponding names from csv 1.

How to join two tables having two common column values and unioning the rest

Try a full outer join, joining on LedgerID and Year, using coalesce to show Table B's LedgerID/Year when Table A's is NULL:

SELECT 
COALESCE(A.LedgerID, B.LedgerID) as LedgerID,
COALESCE(A.Year, B.Year) as Year,
A.Title,
A.Payment,
B.Balance
FROM "Table A" AS A
FULL OUTER JOIN "Table B" AS B ON (A.LedgerID=B.LedgerID AND A.Year=B.Year)

Data intersection in excel

Best to use the VLOOKUP function. See this SO post for a similar question and answer: Join two spreadsheets on a common column in Excel or OpenOffice

You can also use MATCH (I'm not sure if MATCH his more efficient than VLOOKUP). Let's say that Sheet1 contains:

Col A
pass1
pass8
pass3

and that Sheet2 contains:

Col A   Col B
pass1   info1
pass2   info2
pass3   info3
pass4   info4
pass5   info5
pass6   info6
pass7   info7
pass8   info8
pass9   info9
pass10  info10

Then copy the following to cell B1 in Sheet1 =INDEX(Sheet2!B1:B10,MATCH(A1,Sheet2!A1:A10,0),1) then fill down to enter the rest of the rows.

Result in Sheet1:

pass1   info1
pass8   info8
pass3   info3

Hope this helps...good luck, Paul

OpenOffice Compare two cell strings

You should store all values of the first column into an array and then compare every value of the second column with all entries of the array using a simple recursion.

A code that may work is

Dim firstcolumn(5) As String

For i = 0 to 5
   firstcolumn(i) = Sheet.getCellByPosition(0,i)
Next i

For j = 0 to 5
   Cell2 = Sheet.getCellByPosition(1,j)
   for i = 0 to 5
    if Cell2 = firstcolumn(i) then
        MsgBox("The value of the cell " + i + " in the first column is the same with the value of the cell " + j + " of the second column")
    Next i   
Next j

The best place to look for code samples is the openoffice forum https://forum.openoffice.org/en/forum/

I hope that the above will help you.

---

Related Topics