Comparing numbers in one column with numbers in another column.
 

I have a set of serial numbers all in one column and a partial set of the
same numbers in another column. Is there a way to see which numbers from the
first column are not in the second column???

Comparing numbers in one column with numbers in another column.
 

First column A2:A500, second column D2:D200

select A2:A500, do formatconditional formatting, select formula is and use

=COUNTIF($D$2:$D$200,A2)=0

click the format button and select pattern and maybe red colour, click OK
twice. That will highlight the values not in D2:D200

Or use the same formula but in an adjacent column, copy down 500 rows and
apply datafilterautofilter filter on the help column and TRUE,


--
Regards,

Peo Sjoblom



"Smooney" wrote in message
...
I have a set of serial numbers all in one column and a partial set of the
same numbers in another column. Is there a way to see which numbers from
the
first column are not in the second column???

Comparing numbers in one column with numbers in another column.
 

On Wed, 15 Aug 2007 15:58:03 -0700, Smooney
wrote:

I have a set of serial numbers all in one column and a partial set of the
same numbers in another column. Is there a way to see which numbers from the
first column are not in the second column???

Yes there is.

How do you want the results displayed?

You could do it easily with conditional formatting. If the "first" column is
A1:A10, and the "second" column is B then

Select your Range in column A

Format/Conditional Formatting
Formula Is:

=COUNTIF(B:B,A1)=0

Set some format.
--ron

Comparing numbers in one column with numbers in another column
 

Perfect!! Thank you so much. Just one other question. Some of my numbers
in both columns have the letter 'S' in front of them. Is there a way to get
rid of the letter from all the numbers in both columns??

"Peo Sjoblom" wrote:

First column A2:A500, second column D2:D200

select A2:A500, do formatconditional formatting, select formula is and use

=COUNTIF($D$2:$D$200,A2)=0

click the format button and select pattern and maybe red colour, click OK
twice. That will highlight the values not in D2:D200

Or use the same formula but in an adjacent column, copy down 500 rows and
apply datafilterautofilter filter on the help column and TRUE,


--
Regards,

Peo Sjoblom



"Smooney" wrote in message
...
I have a set of serial numbers all in one column and a partial set of the
same numbers in another column. Is there a way to see which numbers from
the
first column are not in the second column???

Comparing numbers in one column with numbers in another column
 

Thank you Ron! I have another question. Some of my serial #'s have an 'S'
in front of them. How do I get rid of the letter?

"Ron Rosenfeld" wrote:

On Wed, 15 Aug 2007 15:58:03 -0700, Smooney
wrote:

I have a set of serial numbers all in one column and a partial set of the
same numbers in another column. Is there a way to see which numbers from the
first column are not in the second column???

Yes there is.

How do you want the results displayed?

You could do it easily with conditional formatting. If the "first" column is
A1:A10, and the "second" column is B then

Select your Range in column A

Format/Conditional Formatting
Formula Is:

=COUNTIF(B:B,A1)=0

Set some format.
--ron

Comparing numbers in one column with numbers in another column
 

On Wed, 15 Aug 2007 16:46:01 -0700, Smooney
wrote:

Thank you Ron! I have another question. Some of my serial #'s have an 'S'
in front of them. How do I get rid of the letter?

A formula that will return a value without a leading S:

=if(left(a1,1)="S",mid(a1,2,255),a1)


--ron

Comparing numbers in one column with numbers in another column
 

"Ron Rosenfeld" wrote...
....
A formula that will return a value without a leading S:

=if(left(a1,1)="S",mid(a1,2,255),a1)

And another one,

=REPLACE(A1,1,LEFT(A1,1)="S","")