Return value of non matches in columns

  • Thread starter Thread starter Gemi
  • Start date Start date
G

Gemi

I am not sure what is the best way to do this: I have two columns that in a
worksheet are usually a few rows off. Column A contains a list of all the
account numbers Column B is missing some of the account numbers. I need to
find the missing accout numbers and enter them in Column C. In the reference
below I would want to know that cell A5 AAC100L is not located in any cell in
Column B. I am looking for non matches between the columns. I tries a couple
of different formulas but they stop when the columns are not an exact match
on the same row.
Any suggestions?
Column A Column B
AAC100A AAC100A
AAC100C AAC100C
AAC100H AAC100H
AAC100L AAC100M
AAC100M AAC100N
AAC100N AAC100P


Thank you,
Lisa
 
In C1 enter =IF(COUNTIF($B$1:$B$6,A1),"", A1 & " is missing")
Copy it down the column
Or use: =IF(ISNA(VLOOKUP(A4,$B$1:$B$6,1,0)),"Missing & A1", "")

best wishes
 
Silly typo in second formula, should be
=IF(ISNA(VLOOKUP(A1,$B$1:$B$6,1,0)),"Missing " & A1, "")
 
Try one of these array formulas** :

Assume your data is in the range A2:B7

The "quick and dirty" method:

Array entered** in C2:

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

Copy down until you get #NUM! errors meaning all data has been extracted.

The "robust" method:

Array entered** in C2:

=IF(ROWS(C$2:C2)<=SUM(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SMALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7,0)),ROW(A$2:A$7)),ROWS(C$2:C2))-MIN(ROW(A2:A7))+1),"")

Copy down until you get blanks meaning all data has been extracted.

** 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.
 
Ooops!

Typo in the "robust" formula.

Should be:

=IF(ROWS(C$2:C2)<=SUM(--(ISNA(MATCH(A$2:A$7,B$2:B$7,0)))),INDEX(A$2:A$7,SMALL(IF(ISNA(MATCH(A$2:A$7,B$2:B$7,0)),ROW(A$2:A$7)),ROWS(C$2:C2))-MIN(ROW(A$2:A$7))+1),"")
 
Back
Top