Want to Verify numbers in Column A exist in Column B

[jasonj427]

I have 2 columns of data sets. I want to ensure that the numbers in column A exist somewhere within the numbers of column B. If they don't, I need to know which ones do not exist? See image attached...

Thanks,
Jason.

[jeffreybrown]

Hi Jason & Welcome to the Forum,

Thank you for the representation of your data, but it is much easier for us to test solution against an actual workbook or we have to type in your data.

Anyway, in C2 try this

=MATCH(B2,$A$2:$A$26,0)

If a value in B is also in A you will get a number which represents the relative position where it is found.

Example, when you put this formula in C2 you will get 2 because the 27.3 in B is found in the 2nd position within A.

[jasonj427]

Thanks heaps for the quick response, similar to what I am after.

However I have over 300 numbers that run down the column A and only 200 down column B. I need to be able to quickly depict which numbers are missing as oppoed to having to work out which numbers do not exist in the sequence once that formula is inserted.

[jeffreybrown]

Once you run the formula down column C, apply a filter to column C and filter for >0

[vlady]

may i budge in.
how about using conditional formatting using that formula(match)???
after all you just want to know which ones do not exist.

[jasonj427]

What I’d like is where column A and Column B have a match, then you’d see that.

Where there is no match, column B would be left blank and all the data would be moved down to find a match.

[jeffreybrown]

Can you attach a sample workbook with a before and after tab please?

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.