Does anyone know how to create a MINIF UDF to find the smallest number
in an array greater than X ?

For example, =MINIF({0,1,2,3},"0") would return a value of 1.

I'm trying to create a formula that will look down the Name column and
determine if there are any duplicate values with common root characters in
the corresponding Code column. The Name's Code with the fewest characters
will be the active code and the longer Codes will be classified as inactive.
Classifying a Name's Code as inactive means it will be ignored in other
calculations.



A B C

1 Code Name Status

2 ABC Apple

3 BCD Banana

4 ABCD Apple



Using the logic below, I'm looking to see if there is more than one Name
equal to Apple and if so, which of the Codes are to be considered active. In
the C column, a formula is used to determine the active/inactive status of
that row's Code.

In C2, I want to create an array with SUMPRODUCT that:

1) Looks for duplicate names in the Name column

B2:B4=B2 would create an array of {1,0,1}

2) Use LEN to determine the number of characters for that row's Code

LEN(A2:A4) would create an array {3,3,4}

3) Multiply these two arrays with a result of {3,0,4}

4) Use MINIF({3,0,4},"0") to get a result of 3

5) Compare the MINIF result with the LEN of that row's Code. If the
values are equal, the Code for that Name's row is active. Otherwise, it is
inactive.

IF(MINIF({3,0,4},"0")=LEN(A2),"Active","Inactive" )



So the sticking point is coming up with a MINIF UDF.

Any ideas ?



- Ronald K.