Trying to cross check Column A with Column B

Hi Everyone, first post here so please take it easy on me. I have tried to search for an answer to my problem but have not had much success. Perhaps I would be able to receive some help here

I currently have a set of numbers in Col A. Each cell in Col A is a unique entry (ie. A1=12345, A2=23584) I need to cross check Col B against Col A to make sure that all of the entries in Col B have a match with Col A. However Col B is not in the same order as Col A (ie. A1=12345, B1=54325, B8=12345) I would like to have Col B checked against A and display all missing unique ID's in Col C. Is this possible?

Essentially Col A is my shipping manifest, Col B is my customers receiving manifest and we want to make sure ea individual item was receieved

Any help towards this would be greatly appreciated!

Cheers!

Edit: Forgot to add the sample of my spreadsheet

hi wliu81, welcome to the forum. please change the formatting of column C to be "General" first. formulas will not be able to be presented in Text format. so currently, everything in column B can be found in column A right? you will get blank for this array formula in C2:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

but if you change some data in Column A, then you will get the missing numbers.

I think this will work. But Im not a formula guy.

Formula:

Please Login or Register  to view this content.

In your sample file there are no missing items! Every item in column B has a match in column A.

I think this is what you want...

This array formula** entered in C2 and copied down until you get blanks:

=IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$22,A$2:A$22,0)),ROW(B$2:B$22)),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This should check if the value in B2 exists in column A, as well as any of the cells above it in column C so it doesn't duplicate a result... Enter it in cell B2 and fill down:

(Regular formula.. not array)

=IF(OR(COUNTIF(A$2:$A$22,B2)>0,COUNTIF($C$1:C1,B2)>0),"",B2)

- Moo

Originally Posted by benishiryo

Please Login or Register  to view this content.

Tip...

Whenever you're trapping errors, you only need to trap the error where it will occur.

In the above formula if an error is generated it will come from the SMALL function so you only need to trap the error at that point:

=IF(ISERR(SMALL(IF(ISNA(MATCH($B$2:$B$22,$A$2:$A$22,0)),ROW($B$2:$B$22)-ROW($C$2)+1),ROWS($C$2:C2))),"",INDEX($B$2:$B$22,SMALL(IF(ISNA(MATCH($B$2:$B$22,$A$2:$A$22,0)),ROW($B$2:$B$22)-ROW($C$2)+1),ROWS($C$2:C2))))

Another tip...

If you index the entire column then you don't need to calculate an "offset correction":

=IF(ISERR(SMALL(IF(ISNA(MATCH($B$2:$B$22,$A$2:$A$22,0)),ROW($B$2:$B$22)),ROWS($C$2:C2))),"",INDEX(B:B,SMALL(IF(ISNA(MATCH($B$2:$B$22,$A$2:$A$22,0)),ROW($B$2:$B$22)),ROWS($C$2:C2))))

However, the OP is using Excel 2007/2010 so we can just use the IFERROR function:

=IFERROR(INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$22,A$2:A$22,0)),ROW(B$2:B$22)),ROWS(C$2:C2))),"")

@Tony Valko
1st tip is good. didnt notice it, thanks

for 2nd tip, i'm always a little reluctant to use the whole column. and for IFERROR, here's part of my signature:

Unless if you ask for an XL2007 formula, i will try to use old formulas so that it can be applied to xls files

I didn't notice that in your sig.

Fair enough!

You should note that formula will only trap errors if the data type being returned is TEXT.