Solved

Comparing in two Excel Spreadsheets using Access

Posted on 2014-03-14
8
794 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 48

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 48

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 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