Solved

Comparing in two Excel Spreadsheets using Access

Posted on 2014-03-14
8
675 Views
Last Modified: 2014-03-16
Hello Expert,

I have two spreadsheets which I would like to compare information on both sheets that match. Then the matching information and details is dumped into a separate spreadsheet.   For instance:  If Sarah Connor with cert number 15622048 is located on both sheet then Sarah Connor with the corresponding information provided is placed in a results spreadsheet as a match.

Also wondering if the same information is provided regularly can it be exported from excel and imported into Access.  Then can a query be used to automate the process.

Thank you fro any assistance and suggestions
Match-Copy.xlsx
0
Comment
Question by:Beeyen
  • 4
  • 3
8 Comments
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 300 total points
ID: 39930684
importing into access using the transferspread function and then querying would be much more efficient. The alternative is to cycle through all rows using vlookup etc.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39931181
I agree with Coachman99,

You would not necessarily need to import the data, linking the two spreadsheets should be sufficient.  Create a query that joins the two tables on FirstName, LastName, and Certificate #, and includes the other pertinent fields from one or both of the tables and export that back out to the workbook as your Matching results.
0
 

Author Comment

by:Beeyen
ID: 39933105
Good Day Gentlemen,

One of you says to import into access using the transferspread function, the other says not to import but to link the two spreadsheets, then create a query that joins the two tables. First, how do I get the data from Excel to Access without importing?  Could you please be more specific.

Thanks
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 39933170
The TransferSpreadsheet method allows you to either import or link the tables, so if you want to do this via code, then that is the method to use, you just have to indicate whether to import or link.

However, if you are only going to do this occasionally, you can do it manually.   On the main ribbon tabs, click on "External Data", then in the Import section, select Excel.

In the wizard that pops up, browse to the Excel file you want to import from, then select either the Import or Link option.  Because it is Excel, Access will link to the Excel tables, but provide a couple of screens for you to identify whether there is a header row, and what you want to call the imported/linked table.  

Microsoft has decided that they know better than you do how to format Excel data coming into Access, so they don't provide you with any options for creating an import specification, defining what fields to import, or what the data types of those fields should be.
0
 

Author Comment

by:Beeyen
ID: 39933227
Thank you for the quick response.

So you are saying if I plan to execute this often I should use the TransferSpreadsheet method?  I did this via the Transferspreadsheet with the code below.  I renamed the spreadsheet attached to CSWMatch and using the code below but the data did not transfer over. What did I do wrong?

Private Sub importExcelSpreadsheet()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"CSWMatch", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True

End Sub
CSWMatch.accdb
0
 

Author Comment

by:Beeyen
ID: 39933237
Good Day,
Okay. I do see where the data imported but only for one spreadsheet.  It appears the data for one spreadsheet doubled.  The workbook has two spreadsheets with different data, but the import seems to have double one spreadsheet into the table. What did I do wrong yet again with the code?  

Let me know

Thanks
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39933308
You will have to use two separate calls to Transferspreadsheet, one for each of the source worksheets.  You will need to give them each separate table names in Access as well, (this is the 3rd argument in the method.  Something like:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet1", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True, "Sheet1!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Sheet2", "C:\Users\Beeyenme\Desktop\CSWMatch.xlsx", True, "Sheet2!"
0
 

Author Comment

by:Beeyen
ID: 39933328
Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question