I have 2 excel spreadsheets both have data that needs to merged. 1 has
item number, catalog page, short description, cost, and retail. The
second has item number and long description. I need to merge the 2
together with the #1 spreadsheet determining what gets merged into
each record by item number. The second spreadsheet has descriptions
for products that are not in #1 and need Not be merged. Is there a way
to do this using
access?

If you import both spreadsheets into Access, then you can do what you
want by creating an Access update query.

I think the easiest way to do it would be to first add a field for
long description to #1, and then make a query by bringing in both
tables, making sure the item numbers are linked by a join, and
updating [table1].[longdescription] to [table2].[longdescription].

The join will, by default, exclude all of the items in #2 that don't
have matching rows in #1; if you had wanted to include everything from
#2 including the new items, you could have done it by modifying the
join properties (by right-clicking on the join in the query.)

I agree with the comment that it's probably possible to do this in
Excel as well, but I personally wouldn't know how, and doing the
import/export shouldn't be at all difficult to handle with Access.

I hope this description is sufficient to explain what you need to do;
please request a clarification if not.

Thanks for using Google Answers!

It took a while to get it. I think it would have helped more if the
answer could have been lined out a little better. But it did give me
the general knowledge to figure it out.

Hi,

you know you could merge the two excel spread sheets directly in Excel
using the vlookup function (it's a bit complicated, but much easier
that getting everything into Access. Have a look at the excel help on
the vlookup function)?

Hope that helps.

Please note that
1) using access, you can also just create a query, joining the 2
tables as described above, and selecting the fields to show in the
order you want. If you are happy with the result as it is shown, you
can change the query type from "select" to "make table" and then run
the query. A new table is created with just the columns you want.
2) using excel, the VLOOKUP function only works when your lookup list
is on the same sheet where you want to use the data (plus the data in
the lookup list must be sorted alphabetically), which can be a problem
if you have a long list of items.

Access is the definitely your best bet, and easier to re-do (re-import
sheets & re-run maketable) if needed.
Makesure to close xls before importing, as sometimes xls files are
locked and won't import into Access at the same time.

HTH

First of all your wording is a little confusing.  It sounds like you
need to import (to import into Access you can just copy and paste or
use the file|get external data|import or link (link is active so if
you make changes to the spreadsheets, or in access, it affects both.
CAUTION) the two files into access.  Then create a select query using
the two files (let's call them Table1 and Table2).  You will need to
join the two tables together in the query by Item #(Click View on the
File menu then Join Properties).  It sound like you want everything
from spreadsheet #1 but only matching Item descriptions from
spreadsheet #2.  If this is the case a default join will do.  You want
to do a left join (table1 points at table 2 with the arrow pointing to
the right.)    To do this once in join properties choose option 2(
make sure that both field names refer to the item# in the top field
and table boxes).  Say OK.  Then Run the query usually by clicking on
the exclamation point on the Query design menu.  If this is the final
result you want then click on query in the file menu then select
make-table query.

I hope this is not to confusing.  You can also use the create query by
using wizard after you have import the two spreadsheets into access.
Good Luck

I would also suggest merging the two tables in excel itself: saves you
a lot of work. Be sure to recopy just the resulting values back into
the final table and not leave the vlookup formula in there. Makes the
xls extremely bulky.

Access is however recommended if you intend to merge tables of about 8
thousand lines or more. I do it regularly at work do to lazyness to
import/export the tables back an forth into access when working on
reports from various SAP downloads and end up with RAM and other
resource crunching 20 Mb xls sheets. As a result I can go to the
coffee machine whenever I run large vlookups

Cheers Oliver