Solved

Comparing in two Excel Spreadsheets using Access

Posted on 2014-03-14
8
632 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
I suddenly cannot write to C drive 20 72
Attachment field in SQL 3 26
Combobox row source 2 20
Allow user to edit an outgoing email 2 24
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

808 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