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

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

[duane]

try thi in column c (in this case for row 3)

=IF(ISERROR(MATCH(B3,A:A,0)),B3,"")

copy as needed

this will generate name in column c which exist in column b but not column a

[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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"maxxwell2" <[email protected]> wrote in message
news:[email protected]...
>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]

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

[maxxwell2]

"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!