compare 2 excel sheets to see if value exists in one or more fields

amkessler
amkessler used Ask the Experts™
on
I have a client who has an existing database that we want to import data from outside sources.  The import relies on the product lookup code. If the imported  data does not have a matching lookup code then a new item would be created in the database, which is not the desired result.  The lookup codes in the vendor supplied data could be in one of 2 columns, I want to create a new sheet with the rows that contain a match  to one of the columns in the original database and to one of the columns in the vendor excel file.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello amkessler,

can you please specify what you mean by "the database"? Is this SQL, Access, or another Excel workbook?

It would be good if you could mock up some sample data and the expected results. It's fairly difficult to provide a formula or macro without anything to start with.

Are you after a VBA or a formula solution?

cheers, teylyn

Author

Commented:
the client is running a Point of Sale system  on SQL,  I exported all the items that the vendor supplies to a csv file, I want to match the Item lookup code from the csv I exported to the ISBN or the vendor product code that the client has used in their database.  I am looking for either a vba or formula,  I just want something to compare the 2 csv files and create a new one with the matches thanks
Most Valuable Expert 2011
Awarded 2010
Commented:
Open the CSV file in Excel. Formula-wise, you can then use something along these lines:

=IF(ISNA(MATCH([VendorBook]Sheet1!$A1,[CSVFile.csv]Sheet1!$A:$A,0)),"",VLOOKUP([VendorBook]Sheet1!$A1,[CSVFile.csv]Sheet1!$A$1:$F$9,2,FALSE))

if you want to copy the fomula to the right, to include more columns, increment the 2 before the False parameter in the vlookup to reflect the column you want to return.

Then copy the formula down and use Autofilter to filter out blank rows.

Now you have a list of records that exist in the original database

cheers, teylyn
TracyVBA Developer

Commented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial