Coimpare A to B and B to A - results in separate columns

I have searched through many threads but can't seem to find one that closely matches what I want to accomplish. Each day I will be comparing a different set of values in Column A and Column B (the number of rows will vary from day to day). I want to identify the values that exist in Column A but not Column B and to identify the values in Column B that do not exist in Column A. I have attached a sample spreadsheet showing a couple of different ways the results could show (Columns C & D or Columns E & F). Could someone look at this and see if there is a way to get the results I want?

Thanks SO much!

Select column A and give it a name of FindA. Name column B FindB. Then in C2, put the following formula and drag it down:

Formula:

Please Login or Register  to view this content.

In D2, put:

Formula:

Please Login or Register  to view this content.

The "missing" results will be scattered through the rows, rather that grouped at the top like your example, but you could sort by C and D to get them to the top. Good luck!

The data is captured from two different sources and if users followed the correct process, Column A should match Column B. So to answer your question, Col A 78943 already has a match in Column B, leaving the 2nd Col A 78943 without a match... It may not be on the same row depending on how the data was extracted and sorted. Does that answer your question?

This is really close. There could be multiples of the same number in Column A or Column B (look at Column A value 78943 for instance). 78943 exists twice in Column A, but only once in Column B. So 78943 should show as a missing number when comparing A to B. The same would apply when comparing B to A. 80227 exists 3 times in Column B, but only twice in Column A, so 80227 should show as missing. This logic makes it more complicated and that's where I'm getting stuck. I suppose it could go through the comparison and delete out matches as it finds them and you would end up with Col A having 78943, 79274, 79552 and 79960 and Col B would have 80227... Does that make sense?

This is exactly what I was looking for. Thank you so much. I will marked this as solved and I can't begin to tell you how impressed my users will be!