Return value of non matches in columns
 

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

Return value of non matches in columns
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gemi" wrote in message
...
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

Return value of non matches in columns
 

Silly typo in second formula, should be
=IF(ISNA(VLOOKUP(A1,$B$1:$B$6,1,0)),"Missing " & A1, "")

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Gemi" wrote in message
...
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

Return value of non matches in columns
 

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,SM ALL(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.

--
Biff
Microsoft Excel MVP


"Gemi" wrote in message
...
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

Return value of non matches in columns
 

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,SM ALL(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),"")


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,SM ALL(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.

--
Biff
Microsoft Excel MVP


"Gemi" wrote in message
...
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