When Two Excel Spreadsheets Look the Same But Are Different…It’s Time For The Access Unmatched Query.

Have you ever had two similar looking spreadsheets in Excel and wanted to find out which rows contain differences? If your lists are small, you could try to view the two spreadsheets either side by side horizontally or vertically. Click Excel’s View tab then click New Window. Then click Arrange All with vertical.

new window and arrange all

With vertical this is what you get.

looks the sameHowever, when your lists contain too many rows to compare in Excel, then you can export the data to Access. Either run the Excel import Wizard from Access or copy and paste between the programs.

find unmatched query wizard

Select the “Find Unmatched Query Wizard” from the Query Wizard button from the Insert Tab in Access.

Step 1

which table or query contains the records you wnat in the query results

Step 2

which table or query contains the related records

Step 3

which piece of information

Step 4

which fields do you want to see

Step 5

name the query

Click Finish and view the dataset. You may need to run the unmatched query wizard again a with changing the table comparison direction if the first query did not produce the results you thought.

record set value

Advertisements

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at 5dollardinners.com.
This entry was posted in Access, Excel and tagged . Bookmark the permalink.

3 Responses to When Two Excel Spreadsheets Look the Same But Are Different…It’s Time For The Access Unmatched Query.

  1. Sjoerd says:

    Hi Steve,
    just read your paper, and found it very interesting indeed.
    Maybe I misinterpretate things, but my first thought was when I read the question on comparison : Spreadsheet compare 2013!
    Maybe yoy should write about that too.

    Thanks for your work

    Sjoerd Jellema, The Netherlands.

  2. Pingback: Locate Differences From Within Two Excel Lists Using the Match Function | Steve Chase Docs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s