Match two columns of data - return mismatches third column

[Silge]

So I have been trying to figure this out.

I have a list of serial numbers in column A that are accurate. I have a list in column B that is inaccurate. The lists are of different sizes and are not in order. I need a formula that compares the numbers in A to the numbers in B and gives the numbers that do not match into column c. I have tried conditional formatting and a couple of other things and none of them have been accurate.

I attached a copy of the sheet I am working on. Any help would be greatly appreciated.





Serial compare.xls

[JieJenn]

Why not use vlookup. In C2, enter =IF(ISERROR(VLOOKUP(B177,$A$2:$A$1033,1,0)),B177,"") and drag it down.

[FDibbins]

try this....
in column C, =IFERROR(VLOOKUP(B2,$A$2:$A$1033,1,FALSE),"") (B compared to A)
in column D, =IFERROR(VLOOKUP(A2,$B$2:$B$1033,1,FALSE),"") (A compared to B)

im working on a CF for this too, so let me know if the above will be what you need?

ok, if you want to use CF, highlight all data in column B, and use this under "use formula" in CF...

=B2=VLOOKUP(B2,$A$2:$A$1033,1,FALSE)

[Silge]

Why not use vlookup. In C2, enter =IF(ISERROR(VLOOKUP(B177,$A$2:$A$1033,1,0)),B177,"") and drag it down.

Wow thanks for the quick response!

So it looks like this one returned numbers that are in column B but not in column A. I need to find the numbers that are in column A but not Column B

[Silge]

try this....
in column C, =IFERROR(VLOOKUP(B2,$A$2:$A$1033,1,FALSE),"") (B compared to A)
in column D, =IFERROR(VLOOKUP(A2,$B$2:$B$1033,1,FALSE),"") (A compared to B)

im working on a CF for this too, so let me know if the above will be what you need?

ok, if you want to use CF, highlight all data in column B, and use this under "use formula" in CF...

=B2=VLOOKUP(B2,$A$2:$A$1033,1,FALSE)

Thanks for the quick reply. I tried this and it did not show just the serial numbers that are in column A but not in column B. I could have done something wrong though.

[JieJenn]

Didn't realize in Column B some of the item has extra spaces. Here try again. Enter the formula with Ctrl + Shift + Enter together and drag it down. My first formula has an error. I apologize.

=VLOOKUP(A2,TRIM($B$2:$B$936),1,FALSE)

[Silge]

Didn't realize in Column B some of the item has extra spaces. Here try again. Enter the formula with Ctrl + Shift + Enter together and drag it down. My first formula has an error. I apologize.

=VLOOKUP(A2,TRIM($B$2:$B$936),1,FALSE)

No need to apologize, I should be apologizing to you haha


Ok so that returned 50 or so serials that are in column A and not in column B so that is great. There is a 138 serial difference in numbers though. Column A has 1033 serials and column b has 894.

Thanks again you have been a huge help

[chcalissie]

I'm trying to use these formulas to find a mismatch of dates between two columns. I only want something (e.g., the word "mismatch") in the column with the result if there is a mismatch. Here is an example of the two columns.

Departure JSA Departure Date RI
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
31-Jul-14 31-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
31-Jul-14 30-Jul-14
31-Jul-14 31-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
30-Jul-14 30-Jul-14
1-Aug-14 30-Jul-14

I've tried the formulas above, and only dates come out whether they match or not. That's not what I want. I tried an IF statement --> =IF(A1 = B1, 0, "mismatch"), but it didn't work.

I'm also comparing a two columns of names and there could be some names in column A that aren't in column B and vice versa. I need to find the names that are missing when comparing the two lists. Any help on that would be appreciated also.

[protonLeah]

chcalissie,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[chcalissie]

I'm very sorry.