Ask Your Question
2

Combine data from Calc sheets that share one common element

asked 2014-12-28 07:01:06 +0200

jrr gravatar image

updated 2014-12-31 01:25:25 +0200

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 ...
(mehr)
edit retag flag offensive close merge delete

Comments

1

"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?

Lupp gravatar imageLupp ( 2014-12-28 14:27:50 +0200 )edit

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?

David gravatar imageDavid ( 2014-12-28 21:06:41 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-12-28 09:52:41 +0200

ROSt52 gravatar image

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 https://help.libreoffice.org/Calc/Spr...

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

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.

edit flag offensive delete link more

Comments

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

Lupp gravatar imageLupp ( 2014-12-28 14:07:29 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2014-12-28 07:01:06 +0200

Seen: 8,540 times

Last updated: Dec 31 '14