Comparing Data Sources

Posted on 2013-10-21
Medium Priority
Last Modified: 2013-10-21
I have 2 Excel files with serial numbers as one of the fields. I need to compare the 2 files to identify if there are any serial numbers on the file A that are not on file B. If the serial number is in both, then I don't need to know, I just need to know when the serial is NOT in both files.

Any help is appreciated.
Question by:jforget1
  • 3

Author Comment

ID: 39588037
I think this can be done in Excel, but if Access is a better option here, I also could do this compare in Access.
LVL 22

Expert Comment

ID: 39588042
Are you looking for a formula?
In file A, you can add a column (or new sheet) with something like this:


Assuming that the serial numbers are in column B of both sheets, if you copy this formula down to match the number of rows in book A, then anything that shows #N/A has a serial number in A but not in B.  Also note that both workbooks must be open for this to happen.

If you want to make it look nicer, you can surround this with an IF like this:

=IF(ISNA( {above formula} ), "Missing in B","")
LVL 41

Accepted Solution

als315 earned 2000 total points
ID: 39588435
In Access it is very simple. Look at sample. ExcelA and ExcelB tables could be your linked Excel files. In first 2 queries missing serials are selected, in union query they are joined

Author Comment

ID: 39588706
In order to make it simpler I have copied the records into the same sheet.
Column A has the serial numbers for all devices, column K is another data source that may or may not match against a record in column A.  So for Column A, which of these does not have a serial number match in column K.  Column H has the formula, but I can't seem to get the syntax right.

Any suggestions.

Author Closing Comment

ID: 39588957
Thanks for the assistance.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

600 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