Matching Items in 2 worksheets
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
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:
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)
To see all answers submitted to the Answer Wiki: View Answer History.
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.