Comparing numbers in one column with numbers in another column.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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???
 
First column A2:A500, second column D2:D200

select A2:A500, do format>conditional 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 data>filter>autofilter filter on the help column and TRUE,
 
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???
Click to expand...

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
 
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??
 
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?
 
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?
Click to expand...

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

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


--ron
 
Ron Rosenfeld said:
A formula that will return a value without a leading S:

=if(left(a1,1)="S",mid(a1,2,255),a1)
Click to expand...

And another one,

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