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

M

maxxwell2

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.
 
E

Earl Kiosterud

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.
 
B

Biff

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
 
M

maxxwell2

Biff said:
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
Click to expand...
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!
 
Top