14

I have two tables, each in it's own sheet in the same file:

Name Age
Alice 18
Bob 19
Carol 20
Dave 19

Name Gender
Bob Male
Dave Male
Eve Female

I want to automatically create a third table (sheet) that contains the joined data on the column name:

Name Age Gender
Bob 19 Male
Dave 19 Male

Is this operation supported in LibreOffice Calc?

Christian
  • 2,174
  • 5
  • 36
  • 54
  • 1
    Third table: just use a cell reference to name and age in the first table. Use VLOOKUP of that row's name in the 2nd table to populate gender. – fixer1234 Dec 28 '16 at 15:47
  • @fixer1234: This should be the answer. Care to write it as an answer? – Jim K Dec 28 '16 at 17:43
  • This tutorial helped me the most: https://www.zyxware.com/articles/4006/vlookup-function-in-openoffice-calc-with-an-example – SurpriseDog Dec 01 '19 at 23:19

1 Answers1

13

Here's a straightforward way to do it.

Sheet 1:

sheet 1

Sheet 2:

sheet 2

Sheet 3:

sheet 3

On Sheet 3, Name and Age are just cell references to Table 1. So A2 contains:

=Sheet1!A2

And B2 contains:

=Sheet1!B2

Gender is populated by a lookup of that row's name in Table 2. So C2 contains:

=VLOOKUP(Sheet3!A2,Sheet2!A:B,2,0)

Note that Tables 1 and 2 don't need to be in the same order.

BTW, these formulas reflect setting preferences to emulate Excel formula syntax in Tools | Options | LibreOffice Calc | Formula.

Revision for comment

Here's a trivial way to adapt this approach for cases where Tables 1 and 2 don't match and you want only the common records. Say Table 2 looks like your example:

sheet 2a

The existing formula would produce this:

sheet 3a

Alice and Carol don't find a match in Table 2. Eve wasn't in Table 1, so there won't be a match for her, either. Turning on filtering lets you hide the #N/A records:

sheet 3b

If you want to have a clean table where those records are actually gone, Copy and Paste Special values (in LO Calc, I selected Text and Numbers but not formulas), will paste only the non-hidden rows. That's shown in rows 10-12 (notice no hidden rows).

fixer1234
  • 27,064
  • 61
  • 75
  • 116
  • 1
    Your example suggests that all names in sheet1 and sheet2 are the same. I want only the data where the name is contained in both sheet1 and sheet2 to be copied into sheet3. Is that also possible? – Christian Dec 28 '16 at 18:28
  • @Christian, I'll drink some more coffee and think about other solutions for LO Calc that do the join more directly. :-) – fixer1234 Dec 28 '16 at 19:00
  • @Christian, Excel (Microsoft) has some tools, as described here: http://superuser.com/questions/420635/how-do-i-join-two-worksheets-in-excel-as-i-would-in-sql. As far as I know, LO Calc doesn't have anything comparable. I'm guessing this would be easy via importing the data into LO Base, but I've never used that. – fixer1234 Dec 28 '16 at 19:34
  • @Christian, I found a similar question for Excel on SO: http://stackoverflow.com/questions/25657541/merge-two-excel-tables-based-on-matching-data-in-columns. The solutions people came up with there are similar to my original answer, but testing for the error and replacing it with a blank cell. – fixer1234 Dec 28 '16 at 19:44
  • @Christian, there are some add-in tools for Excel. For example see: http://www.digdb.com/excel_add_ins/join_merge_tables_lists/. The add-in availability for LO Calc is pretty limited, but that would be another avenue to check. – fixer1234 Dec 28 '16 at 19:48
  • Another approach in Excel that doesn't have an equivalent in LO Calc is using pivot tables. For example: https://computers.tutsplus.com/tutorials/advanced-pivottables-combining-data-from-multiple-sheets--cms-21190 – fixer1234 Dec 28 '16 at 20:05
  • 2
    To make this work I changed the vlookup to this: =VLOOKUP(A1;$Sheet2.A$1:B$76;2;0) – Jogai Mar 14 '19 at 09:14
  • @Jogai, LO Calc has a choice of settings for formula syntax. You can set it to follow the Excel practice of using `!` to delimit the sheet name, which makes it more compatible with Excel formulas (`Tools | Options | LibreOffice Calc | Formulas | Formula Options | Formula Syntax`). There is also a locale difference that uses comma in some countries and semicolon in others as a parameter separator in formulas. A lot of the spreadsheet answers on Super User require "translating" for locale differences, which is an ongoing source of confusion if readers aren't aware of the difference. :-) – fixer1234 Mar 14 '19 at 16:47
  • @fixer1234 but the answer does not explain you suppose to use Excel syntax. The question is about Libre Office not Libre Office with Excel Syntax Formula enabled. – user149244 Jun 15 '23 at 12:53