How can I check that the data in two different Excel spreadsheets is the same?

I have two long lists of things and I want to make sure that they are the same, and if they aren't the same i want to see what data is missing. Is this possible? Thank you for your help.How can I check that the data in two different Excel spreadsheets is the same?
Are they separate sheets or separate workbooks? It would be easier if they were in the same book. Anyhow, my suggestion is that since you are dealing with text do a =COUNTIF(A1:E20,H29) where the range is all your data and in the criteria cell, H29 in my test case, put *e* or any other character.



If your worksheets are in the same book have a formula referring to each sheet. If there is any difference between data it should soon show up in the number of characters. Not 100% certain but a good indication.



If there are differences, you will get an indication of what text is missing and from which sheet. If you run a series of vowels, it will practically spell it out for you. Of course you can type in a whole word, if you think you know what it is.How can I check that the data in two different Excel spreadsheets is the same?
Will data be in exactly the same cells?



ie are we checking is

spreadsheet1 cell A1 = spreadsheet2 A1



or are you wanting to know if spreadsheet1 cell A1 appears anywhere is spreadsheet2?