=IF(SUMPRODUCT macro question

[hoyaguru]

I've been reading through previous posts and have not been able to fin
an answer to my problem, any help would be greatly appreciated.

I have a list of part numbers for laptop batteries corresponding to al
of the laptops they work in, so one part number may show up severa
hundred times.

I receive a daily list showing which part numbers are available an
which are backordered. I run a macro that takes each part number fro
the backorder file and compares it to the parts I sell. For example:

A
B-5630
AC-C10
B-5666
DK-C25

Column B is where I put the list of part numbers that are out of stock
Column E has my formula that is supposed to compare the part numbers i
B to A, which is:

=IF(SUMPRODUCT(ISNUMBER(SEARCH(B2,A1:A6))+0)>0,B2,"")

Here is my problem: Using the first part number above, B-5630, i
B-5630 is in column B, it will show up in column E. But, if only par
of the number is in column B (ie B-563, B-5, even just B), that wil
show up in E also. I need to make is so that only the exact number wil
show up in E, any suggestions? Thanks in advance! Oh, I just noticed
can attach a file, I will attach a small sample of what I work with

Attachment filename: macro.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51479

 

[Dave Peterson]

First, you can attach files via excelforums, but a lot of people connect to the
microsoft servers directly and won't see your attachment. And many won't open
any attachments--too many threats of malicious code.

But maybe you could use a simpler formula if you're looking for an exact match:

=IF(COUNTIF(A1:A6,B2)>0,B2,"")

 

[hoyaguru]

Well, actually I figured it out with: =IF
ISERROR((MATCH(B2,$A$2:$A$4,0))),"",B2) but now i have a differen
problem, I'm going to have to post another question