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.
For unsubscribe instructions e-mail to: email@example.com
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy