Extracting Values on one list and not another

I obtained a formula from www.cpearson.com for extracting values on one list
and not another.

I put 16717 rows of part numbers in cells A2:A16717. I put another list of
part numbers in cells B2:B16717.

I entered the formula =IF(COUNTIF($A$2:$A$16717,B2)=0,B2,"") into cell C2
and used the fill corner to enter the formula in C cells down to C16717.
(which it the one from the web site)

Shouldnt I be getting the values that on on the B cell list but not in the A
cell list?
Seems like I was, ( had to keep putting new numbers in the B cell list over
and over to get a new extracted list in C cells - needed to sort over 60,000
part numbers against A cell list ) Then I noticed I was getting alot of
duplicate numbers and blank cells in the extracted list.

Also had another problem, some of the part numbers are the same in A cell
list and B cell list and shouldnt show up in the extracted list but they are
due to a key entry.
Example:
dm-002-03 in Acell list
DM-002-03 in Bcell list

Anyway to overcome these problems?

Thanks Bunches,
Barbara

Hi Barbara

i did exactly as described in your post, even down to the dm-002-03 Vs
DM-002-03 and it worked fine (DM-002-03 didn't show up in column C) - maybe
there's spaces before or after your data (if there are and you can afford to
remove all spaces in the part numbers columns, select columns A & B, choose
edit / replace - click in the first box and press your spacebar, and then
click on replace all) ... this should solve the problem of an extra space.

other than that i'm not sure what could be going wrong for you

Cheers
julieD



"B Schwarz" <[email protected]> wrote in message
news:[email protected]...
>I obtained a formula from www.cpearson.com for extracting values on one
>list
> and not another.
>
> I put 16717 rows of part numbers in cells A2:A16717. I put another list of
> part numbers in cells B2:B16717.
>
> I entered the formula =IF(COUNTIF($A$2:$A$16717,B2)=0,B2,"") into cell C2
> and used the fill corner to enter the formula in C cells down to C16717.
> (which it the one from the web site)
>
> Shouldnt I be getting the values that on on the B cell list but not in the
> A
> cell list?
> Seems like I was, ( had to keep putting new numbers in the B cell list
> over
> and over to get a new extracted list in C cells - needed to sort over
> 60,000
> part numbers against A cell list ) Then I noticed I was getting alot of
> duplicate numbers and blank cells in the extracted list.
>
> Also had another problem, some of the part numbers are the same in A cell
> list and B cell list and shouldnt show up in the extracted list but they
> are
> due to a key entry.
> Example:
> dm-002-03 in Acell list
> DM-002-03 in Bcell list
>
> Anyway to overcome these problems?
>
> Thanks Bunches,
> Barbara

Julie,
I will rid the spaces and align all the numbers the same and give it another
try.
Will let you know if it works,
thanks,
Barb

"JulieD" wrote:

> Hi Barbara
>
> i did exactly as described in your post, even down to the dm-002-03 Vs
> DM-002-03 and it worked fine (DM-002-03 didn't show up in column C) - maybe
> there's spaces before or after your data (if there are and you can afford to
> remove all spaces in the part numbers columns, select columns A & B, choose
> edit / replace - click in the first box and press your spacebar, and then
> click on replace all) ... this should solve the problem of an extra space.
>
> other than that i'm not sure what could be going wrong for you
>
> Cheers
> julieD
>
>
>
> "B Schwarz" <[email protected]> wrote in message
> news:[email protected]...
> >I obtained a formula from www.cpearson.com for extracting values on one
> >list
> > and not another.
> >
> > I put 16717 rows of part numbers in cells A2:A16717. I put another list of
> > part numbers in cells B2:B16717.
> >
> > I entered the formula =IF(COUNTIF($A$2:$A$16717,B2)=0,B2,"") into cell C2
> > and used the fill corner to enter the formula in C cells down to C16717.
> > (which it the one from the web site)
> >
> > Shouldnt I be getting the values that on on the B cell list but not in the
> > A
> > cell list?
> > Seems like I was, ( had to keep putting new numbers in the B cell list
> > over
> > and over to get a new extracted list in C cells - needed to sort over
> > 60,000
> > part numbers against A cell list ) Then I noticed I was getting alot of
> > duplicate numbers and blank cells in the extracted list.
> >
> > Also had another problem, some of the part numbers are the same in A cell
> > list and B cell list and shouldnt show up in the extracted list but they
> > are
> > due to a key entry.
> > Example:
> > dm-002-03 in Acell list
> > DM-002-03 in Bcell list
> >
> > Anyway to overcome these problems?
> >
> > Thanks Bunches,
> > Barbara
>
>
>

Julie,
Not working. When I test the list against one another I am still getting the
same part number on both lists. Could it be this...List B does have some
duppicate numbers.
Example
List A has part numbers that have been converted to pdf

List B has all part numbers and for every page it lists the part number,
like if there is a part number that has 3 pages for the print it lists the
part number 3 times.
Could this be causing Excel problems when checking the list?

Barb

"B Schwarz" wrote:

> Julie,
> I will rid the spaces and align all the numbers the same and give it another
> try.
> Will let you know if it works,
> thanks,
> Barb
>
> "JulieD" wrote:
>
> > Hi Barbara
> >
> > i did exactly as described in your post, even down to the dm-002-03 Vs
> > DM-002-03 and it worked fine (DM-002-03 didn't show up in column C) - maybe
> > there's spaces before or after your data (if there are and you can afford to
> > remove all spaces in the part numbers columns, select columns A & B, choose
> > edit / replace - click in the first box and press your spacebar, and then
> > click on replace all) ... this should solve the problem of an extra space.
> >
> > other than that i'm not sure what could be going wrong for you
> >
> > Cheers
> > julieD
> >
> >
> >
> > "B Schwarz" <[email protected]> wrote in message
> > news:[email protected]...
> > >I obtained a formula from www.cpearson.com for extracting values on one
> > >list
> > > and not another.
> > >
> > > I put 16717 rows of part numbers in cells A2:A16717. I put another list of
> > > part numbers in cells B2:B16717.
> > >
> > > I entered the formula =IF(COUNTIF($A$2:$A$16717,B2)=0,B2,"") into cell C2
> > > and used the fill corner to enter the formula in C cells down to C16717.
> > > (which it the one from the web site)
> > >
> > > Shouldnt I be getting the values that on on the B cell list but not in the
> > > A
> > > cell list?
> > > Seems like I was, ( had to keep putting new numbers in the B cell list
> > > over
> > > and over to get a new extracted list in C cells - needed to sort over
> > > 60,000
> > > part numbers against A cell list ) Then I noticed I was getting alot of
> > > duplicate numbers and blank cells in the extracted list.
> > >
> > > Also had another problem, some of the part numbers are the same in A cell
> > > list and B cell list and shouldnt show up in the extracted list but they
> > > are
> > > due to a key entry.
> > > Example:
> > > dm-002-03 in Acell list
> > > DM-002-03 in Bcell list
> > >
> > > Anyway to overcome these problems?
> > >
> > > Thanks Bunches,
> > > Barbara
> >
> >
> >

Hi

when i test it on a few numbers the duplicates in B don't appear in C, so,
if you'ld like to zip the file up and email it direct to me (julied_ng at
hcts dot net dot au) i'll be happy to have a look at it for you

Cheers
JulieD

"B Schwarz" <[email protected]> wrote in message
news:[email protected]...
> Julie,
> Not working. When I test the list against one another I am still getting
> the
> same part number on both lists. Could it be this...List B does have some
> duppicate numbers.
> Example
> List A has part numbers that have been converted to pdf
>
> List B has all part numbers and for every page it lists the part number,
> like if there is a part number that has 3 pages for the print it lists the
> part number 3 times.
> Could this be causing Excel problems when checking the list?
>
> Barb
>
> "B Schwarz" wrote:
>
>> Julie,
>> I will rid the spaces and align all the numbers the same and give it
>> another
>> try.
>> Will let you know if it works,
>> thanks,
>> Barb
>>
>> "JulieD" wrote:
>>
>> > Hi Barbara
>> >
>> > i did exactly as described in your post, even down to the dm-002-03 Vs
>> > DM-002-03 and it worked fine (DM-002-03 didn't show up in column C) -
>> > maybe
>> > there's spaces before or after your data (if there are and you can
>> > afford to
>> > remove all spaces in the part numbers columns, select columns A & B,
>> > choose
>> > edit / replace - click in the first box and press your spacebar, and
>> > then
>> > click on replace all) ... this should solve the problem of an extra
>> > space.
>> >
>> > other than that i'm not sure what could be going wrong for you
>> >
>> > Cheers
>> > julieD
>> >
>> >
>> >
>> > "B Schwarz" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >I obtained a formula from www.cpearson.com for extracting values on
>> > >one
>> > >list
>> > > and not another.
>> > >
>> > > I put 16717 rows of part numbers in cells A2:A16717. I put another
>> > > list of
>> > > part numbers in cells B2:B16717.
>> > >
>> > > I entered the formula =IF(COUNTIF($A$2:$A$16717,B2)=0,B2,"") into
>> > > cell C2
>> > > and used the fill corner to enter the formula in C cells down to
>> > > C16717.
>> > > (which it the one from the web site)
>> > >
>> > > Shouldnt I be getting the values that on on the B cell list but not
>> > > in the
>> > > A
>> > > cell list?
>> > > Seems like I was, ( had to keep putting new numbers in the B cell
>> > > list
>> > > over
>> > > and over to get a new extracted list in C cells - needed to sort over
>> > > 60,000
>> > > part numbers against A cell list ) Then I noticed I was getting alot
>> > > of
>> > > duplicate numbers and blank cells in the extracted list.
>> > >
>> > > Also had another problem, some of the part numbers are the same in A
>> > > cell
>> > > list and B cell list and shouldnt show up in the extracted list but
>> > > they
>> > > are
>> > > due to a key entry.
>> > > Example:
>> > > dm-002-03 in Acell list
>> > > DM-002-03 in Bcell list
>> > >
>> > > Anyway to overcome these problems?
>> > >
>> > > Thanks Bunches,
>> > > Barbara
>> >
>> >
>> >