Matching Items in 2 worksheets

5 pts.
Tags:
Excel 2007
Excel 2007 workbooks
This is probably incredibly basic and has a simple answer - at least I hope it does.  I am using Excel 2007.

I have 2 worksheets run from 2 different systems.  The systems are supposed to have the exact same information, but unfortunately Sheet A has 1350 results and Sheet B has 1365 results.  I am trying to identify which items are in Sheet B that are not in Sheet A.  How would I do this?  Similarly, I also want to know which items are matched/paired in both sheets.

Thanks in advance!!!!



Software/Hardware used:
Excel 2007
ASKED: March 7, 2010  10:51 PM
UPDATED: March 8, 2010  9:52 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you have both spreadsheet A and B on the same computer then you can use the VLOOKUP formula to figure it out.

Open both of the spreadsheets. On the spreadsheet with more items, insert a column next to the items and enter in the VLOOKUP formula. When you are done it would look something like what is below.

=VLOOKUP(A1,[Book2]Sheet1!$A:$B,2,FALSE)

For a quick tutorial on VLOOKUP you can go to:

http://office.microsoft.com/en-us/excel/hp052093351033.aspx

After you are done dragging this formula down to match all items, select the column, copy, paste special (selecting “values” as what you want to paste). With that same column selected, hit CTRL+H (or use the Home ribbon, Editing block, Find and Select, Replace…)

In the Find what box, enter “#N/A”
Leave the replace with box empty, and click Replace All.

Now sort by that column and you will find your mysterious 15.

Let us know how it goes. Good luck (This answer may sound long but it does not take a great amount of time)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Chippy088
    Yes, it is a fairly basic problem. One which many admins have to resolve at some time or another. Although XENOPHON22' answer will do the job, if both spreadsheets should hold the same information, the fact that they aren't synchronised means that you need to find out how the differences came about. I had the same problem with over 15,000 cells that should have been exact copies. It seemed that both sheets were in different books, and because 1 was in use and locked, the other was accessed and ammended. My point is that both sheets may have different data, you can not assume that the longest is the most upto date. I would strongly suggerst that you use XENOPHON22' answer, but apply it to both sheets and compare the results.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following