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:
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)
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)
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.
(LibreOffice 184.108.40.206, Calc running on OSX 10.9.5)