Joe and Dave,
Thanks for those quick replies.
Unfortunately, the curly braces within the parentheses of my second
post weren't very noticeable.
In my haste to post, I incorrectly described the problem.
Here's an accurate, albeit more involved example.
A grocery store is going through its inventory and is interested in
tracking only certain types of items.
For example, each type of fruit is assigned a code. When a
different vendor is used to obtain that type of fruit, the previous
vendor's code is retired by appending a letter or number to that code
(i.e., OA changes to OA1). The new vendor is assigned the original
code (i.e., OA). Over time, retired codes appear many times in the
inventory.
The goal is to look for duplicates and return a value of
"Duplicate" where that code will be ignored in other formulas.
A B
1 Code Name
2 A Apple
3 P Pear
4 OA Orange
5 OA1 Orange
Two named ranges exist:
Codes = $A$2:$A$5
Names = $B$2:$B$5
In C2:C5, the following formula is entered:
=IF(COUNTIF(Names,$B2)=1,"",IF(SUMPRODUCT(SMALL(INDEX(Names=
$B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(Codes),),
2))>0,"","Duplicate"))
In cells C2 and C3, the formula correctly returns "".
However, in cell C4, the formula returns "Duplicate", because out
of {0,0,1,2}, 0 is the second smallest number.
What I'm trying to do is get the formula to exclude the 0's and
return the smallest number in that set.
- Ronald K.