Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
B Schwarz
 
Posts: n/a
Default 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
  #2   Report Post  
JulieD
 
Posts: n/a
Default

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" wrote in message
...
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



  #3   Report Post  
B Schwarz
 
Posts: n/a
Default

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" wrote in message
...
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




  #4   Report Post  
B Schwarz
 
Posts: n/a
Default

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" wrote in message
...
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




  #5   Report Post  
JulieD
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"