Combine data from Calc sheets that share one common element

[jrr]

What I have are two (or more) sheets that have columns of data (part numbers) and the problem I have is trying to merge the sheets into a third sheet where the part numbers from one are matched to the part number of the second and the corresponding data (prices, description, etc.) is put into respective columns. There are several thousand part numbers…

As an example one sheet has a part number, German label and a price in Euros, the other sheet may have that part number (or not), it has an older Euro price and an English description.

My goal is to have a sheet with all the part numbers from both lists in sequence, and numbers that match have the information from each sheet, and numbers that don’t match are still present with the data from their sheet something like:

Part Number | Old Euro price | New Euro Price | German Desc. | English Desc. | other columns...
1           |     12         |   18           | (German text)| (English text)|  other data
2           |  (no data)     |   5            | (German text)| (nothing)     | (nothing ) 
14          |      32        | (no data)      | (nothing)    | (English text)| etc...

…

So part number 1 would have matches on both sheets, number 2 only on the second sheet, 14 on the first sheet and so on, some matching one part number or the other or both.

I was trying to do this in Base (I understand this would be better, but even less of a clue), but don’t understand how to do Inquiries enough to build the resulting data format. I was able to import the Calc sheets into separate Base databases, but got stuck at that point.
Thanks for looking at this!

More info added Dec 30:

Well, I’ve played with VLOOKUP() but I’m not getting the results needed. You see the two sheets, while they share a number of part numbers, there are missing numbers on each sheet. What I am trying to do is generate a master sheet that combines the two other sheets into a full list of both part numbers - where the part numbers are the same, then the columns share have the relevant data. Where the part numbers are missing on one, but not the other sheet, then I just want the existing part number with its data on its row.

The formula I tried with was VLOOKUP($A2,SHEET1.$A2, SHEET2.$A2:$C4,2,0) (depending on the column - this is for column 3 on Sheet 3) - this only worked for the first row, then I got #N/As once it got to missmatched part numbers.

Let me make two simple lists and the results I am looking for:

first sheet:

A0          |  B0            | C0
Part Number | Old Euro price | German Desc. 
1           |     12         |   (German text)
13          |     19         |   (German text)
27          |     21         |   (German text)
33          |      2         |   (German text)

second sheet:

A0          | B0             |C0
Part Number | USD            | English Desc. 
1           |     10         |   (English text)
17          |     15         |   (English text)
27          |     19         |   (English text)
44          |      1.5       |   (English text)

Combined sheet

A0          |B0              |C0         |D0             |E0
Part Number | Old Euro price | USD Price | German Desc.  | English Desc.
1           |     12         |     10    |(German text)  | (English text)
13          |     19         |           |(German text)  |
17          |                |     15    |               | (English text)
27          |     21         |     19    |(German text)  | (English text)
33          |      2         |           |(German text)  |
44          |                |   1.5     |               | (English text)

So, as I hope you can see, the final sheet has figured out the rows and columns for the missing and combined part numbers.

That is the result I’m after.

Thanks!

(LibreOffice 4.2.6.3, Calc running on OSX 10.9.5)

[Lupp]

“Combine data … that share one common element” as given in the subject and (later on) “My goal is to have a sheet with all the part numbers from both lists in sequence” (as explained by the example that obviously contains 2 rows to which one or another sheet does not contribute) seem not to be compatible intentions to me. Might you explain this more precisely, please?

In addition: Is there already a sheet containing all the part numbers occurring anywhere?

[dajare]

Do you have access to other languages/technologies? Spreadsheet might not be the best option here. If you can save to CSV files, there are plenty of articles Google will find for you on merging CSV files with related headers in perl, python, R, etc. Possible?

[ROSt53]

I would use the vertical lookup function in Calc.

First backup your file

Next select the list with most entries and add the others to this list using the vertical lookup function.

Use the Help button in Functions, or Spreadsheet Functions - LibreOffice Help

I also recommend to have a look at the free guides which you can download from http://www.libreoffice.org/get-help/documentation/

If you still have question please provide good explanations on what you did (step by step) and what was the result.

If you have too many entries, shorter test tables might bey useful.

[Lupp]

Did you consider that
VLOOKUP()
will have to find the match for every column anew?