Extracting Values on one list and not another

[Guest]

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

 

[JulieD]

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

 

[Guest]

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

 

[Guest]

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

 

[JulieD]

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