Re: [libreoffice-users] Merging two different spreadsheets in Calc

Hi :)
There is a neat little chapter in the "Getting Started with LibreOffice" Guide
https://wiki.documentfoundation.org/Documentation/Publications
Also a full guide dedicated to Calc that is worth dipping into.  I don't think the in-built help 
files are much good but odd questions to the list can help you get to grips with the documentation. 
 
Regards from
Tom :)

--- On Wed, 11/4/12, Brian Barker <b.m.barker@btinternet.com> wrote:

From: Brian Barker <b.m.barker@btinternet.com>
Subject: Re: [libreoffice-users] Merging two different spreadsheets in  Calc
To: users@global.libreoffice.org
Date: Wednesday, 11 April, 2012, 5:14

At 13:30 10/04/2012 -0700, Randy Zimmer wrote:
> Caution, I know nothing about spreadsheet programs.

It's perhaps worth saying that you probably cannot conveniently learn how to construct a 
spreadsheet by asking general questions on a mailing list such as this.  You would probably be well 
advised to learn about spreadsheets somehow - perhaps by reading the help text, consulting help 
locally from friends or colleagues, reading guides (perhaps from the library), or attending a 
course.

> I get a .txt from our supplier every month with 101,961 part numbers, prices and more.  I have a 
> list of items we stock or have stocked - it also has part numbers but also has where we put them 
> and how many we have - this has 1280 numbers.  What I want to do is make one list with all the 
> info from the two.  I have two files with the data in .ods format.

You might find it convenient first to copy or import the data in one file as a second sheet within 
the other file, but this is not necessary to achieve what you desire, in fact: you can refer to 
data in the other document file directly if you prefer.

> Can they be merged based on the part number?  That's the only common info and they start at top 
> left on both files.

What exactly do you hope to see?  I'm guessing that your data is in columns and that you want a 
copy of the larger list - with however many columns that already has - along with extra columns to 
contain the information from the smaller list where a row for that particular item exists in that 
list.  You can do this by adding formulae in *all* the rows of the additional columns in the larger 
list that will carry the data harvested from the smaller list.  The VLOOKUP() function will search 
for and retrieve the data you need.

o The first parameter of the function needs to be the search criterion: in your case, this will 
simply be the part number - probably in your first column.
o The second parameter is the array that will be searched: this is the whole of the data area of 
your second, smaller list.
o The third parameter is the index of the column in your smaller list that will be picked up in 
each case: this may well be 2 for your first additional column, 3 for your second, and so on.
o The fourth parameter needs to be FALSE (or zero) in order that rows that do not have 
corresponding data in the second sheet return an error instead of a nearby value.

A few extra notes:
o If you construct the formula carefully in one cell, you can copy or fill it into many columns of 
your very many rows and the spreadsheet will modify the formula as it is copied so as to create a 
unique version in each cell that will achieve what you need.
o To prevent references to your smaller list data from moving as the formula is filled or copied, 
you will need judicious inclusion of the "$" character to freeze those references.
o With the fourth parameter set to FALSE, your formulae will return #N/A (meaning "value not 
available") where there is no corresponding row in the smaller list.  If you want such cells 
instead to be empty, you could use the ISNA() function to recognise this case and replace the error 
indication with nothing.

So your final formula (in the first row of the first additional column) may look something like 
this:
 =IF(ISNA(VLOOKUP(A1;<stocklist>;2 ;0));"";VLOOKUP(A1;<stocklist>;2 ;0))
If the VLOOKUP() fails to find a value, the error value is replaced by a null string; otherwise the 
VLOOKUP() value is used.  It would be the reference to your stock list that would have to contain 
the $ signs: if it extended from A1 to (perhaps?) C1280, then the array would have to appear as 
$A$1:$C$1280 (not just A1:C1280).

As I say, you would have to gain some fluency with using spreadsheets before you could make any use 
of what I have suggested.

I trust this helps.

Brian Barker


-- For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted


-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted