Find a value in list 2 that is not in list 1
 

I have text values in column A and also in column B, most of which match. I
would like to take the values in column B that are not in column A and put
those values only in column C. Any help would be greatly appreciated.

Maxxwell,

Put this formula in column C, and copy down.
=IF(ISERROR(MATCH(B2,$A$2:$A$20,0)),B2,"")

To get them contiguous, use Autofilter, filter on nonblanks in column C, and
copy/paste to somewhere.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"maxxwell2" wrote in message
...
I have text values in column A and also in column B, most of which match. I
would like to take the values in column B that are not in column A and put
those values only in column C. Any help would be greatly appreciated.

Hi!

Here's an alternative method that doesn't require the
extra steps.

Assume the lists are in the range A1:B10. Enter this
forumla in C1 with the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$10,$B$1:$B$10 )=0,ROW
($A$1:$A$10)),ROW(1:1))),"",INDEX($B$1:$B$10,SMALL (IF
(COUNTIF($A$1:$A$10,$B$1:$B$10)=0,ROW($A$1:$A$10)) ,ROW
(1:1))))

Copy down until you get blanks.

OR, use the above formula without the error trap, array
entered:

=INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A$1:$A$10,$B$1 :$B$10)
=0,ROW($A$1:$A$10)),ROW(1:1)))

Copy down until you get a #NUM! error.

Biff

-----Original Message-----
I have text values in column A and also in column B, most
of which match. I
would like to take the values in column B that are not in
column A and put
those values only in column C. Any help would be greatly
appreciated.
.

"Biff" wrote:

Hi!

Here's an alternative method that doesn't require the
extra steps.

Assume the lists are in the range A1:B10. Enter this
forumla in C1 with the key combo of CTRL,SHIFT,ENTER:

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$10,$B$1:$B$10 )=0,ROW
($A$1:$A$10)),ROW(1:1))),"",INDEX($B$1:$B$10,SMALL (IF
(COUNTIF($A$1:$A$10,$B$1:$B$10)=0,ROW($A$1:$A$10)) ,ROW
(1:1))))

Copy down until you get blanks.

OR, use the above formula without the error trap, array
entered:

=INDEX($B$1:$B$10,SMALL(IF(COUNTIF($A$1:$A$10,$B$1 :$B$10)
=0,ROW($A$1:$A$10)),ROW(1:1)))

Copy down until you get a #NUM! error.

Biff

-----Original Message-----
I have text values in column A and also in column B, most
of which match. I
would like to take the values in column B that are not in
column A and put
those values only in column C. Any help would be greatly
appreciated.
.


Thank very much for the formula, it works fine, I was just hoping there
might be a cleaner way to accomplish this task. Also my list is approximately
250 rows long and when I use your formula, it finds the proper values(the
first 45 rows) then the next 90 rows or so, it inserts a value of 0, and the
last 90 rows are blank. Do you know why I am getting a zero value in these
middle rows? THANKS AGAIN!