Finding MIN text value from array excluding certain specified values

[logonwheeler]

I am looking to return the MIN alphabetical text value from a list, with certain values excluded.

I am currently using an array formula like this one to return the MIN alphabetical value in array A1:A9:

=INDEX($A$1:$A$9,MATCH(0,COUNTIF($A$1:$A$9,">"&$A$1:$A$9),)) (+ CSE)
Returns the value A

However, I would like to return the MIN alphabetical value in the array EXCLUDING all A & D values. This result should return the value B.

Can anyone suggest a nice way to achieve this?

Data Set:
1 A
2 D
3 A
4 C
5 B
6 D
7 C
8 B
9 A

 

[Aladin Akyurek]

Would you share the data and specs for which the COMP EVAL issue arises?

 

[markmzz]

Markmzz,

Your solution is working (mostly). However it is treating "COMP EVAL" later in the alphabetic list than P5, P4, P3, P2, P1, & PROTOTYPE. Shouldn't it be treating it as 1st in the alphabetical list?

Try to remove all exclusions from the list and input 1, 2, 3, 4, & 5 in the specification input box. You will be this behavior demonstrated.

Which has me thinking. Would it be better to have a "VALUES" list that is manually sorted so we do not have to rely on alphabetical order? That would make the formula more robust to handle many use cases. Any ideas / opinions on that?

Thanks.

Hi Logonwheeler,

Did you see the posts #35 and #38?

If yes, what is your feedback?

Markmzz

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

= IF(SUM(IF(A2:A16=D2,IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),1))))>0,INDEX(B2:B16,MATCH(MAX(MMULT(IF(A2:A16=D2,IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),(B2:B16<=TRANSPOSE(IF(A2:A16=D2,IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),B2:B16,0),0),0)))+0,0),0),0),ROW(B2:B16)^0)), MMULT(IF(A2:A16=D2,IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),(B2:B16<=TRANSPOSE(IF(A2:A16=D2,IF(B2:B16<>"",IF(ISNA(MATCH(B2:B16,F2:F5,0)),B2:B16,0),0),0)))+0,0),0),0),ROW(B2:B16)^0),0)),"N/A")

Hope this helps!

 

[Marcelo Branco]

Marcelo - Working quite well. The only issue I found so far is that this formula is treating the result "COMP EVAL" later than all the P5, P4, P3 etc values. Do you know why this is?

It worked perfectly for me

	A​	B​	C​	D​	E​	F​
1​	SPECIFICATION #​	VALUES​		SPECIFICATION #​	RESULT​	EXCLUSION LIST​
2​	1​	COMP EVAL​		1​	COMP EVAL​	P5​
3​	1​	P5​				P1​
4​	1​	P2​				P3​
5​	2​	P4​
6​	2​	P4​
7​	2​	P3​
8​	3​	PROTOTYPE​
9​	3​	P5​
10​	3​	P1​
11​	4​	P3​
12​	4​	P2​
13​	4​	P1​
14​	5​	COMP EVAL​
15​	5​	P2​
16​	5​	P1​

<TBODY>
</TBODY>

Array Formula in E2
=IF(SUM(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),IF($B$2:$B$16<>"",1))))>0,INDEX($B$2:$B$16,MATCH(MIN(IF(ISNUMBER(MATCH($A$2:$A$16,$D$2:$D$16,0)),IF(B2:B16<>"",IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16))))),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16),0)),"N/A")

Ctrl+Shift+Enter

M.

 

[Fazza]

It might be easier to use SQL for this - SQL is made for working with datasets.

This gives the full dataset - the minimum for each 'specification #' value. I've used defined names "tblMain" and "tblExclusions" for the source data.

SELECT A.[SPECIFICATION #], MIN(A.VALUES) AS [VALUES]
FROM (SELECT M.[SPECIFICATION #], M.VALUES, E.[EXCLUSION LIST]
FROM {oj tblMain M LEFT OUTER JOIN tblExclusions E ON M.VALUES = E.[EXCLUSION LIST]}
WHERE E.[EXCLUSION LIST] Is Null) A
GROUP BY A.[SPECIFICATION #]

Is this sort of efficient solution of interest, logonwheeler? 'Cause it looks like the formula solutions are getting too much.

 

[Marcelo Branco]

logonwheeler

Domenic kindly, by a PM, warned me that I was not understanding what you really are looking for.

I thought that, for example, if you input 1 and 2 in column D you were wanting just one formula returning the MIN for Specifications 1 OR 2. In other words, just one result, the MIN for both specifications.

But reading more accurately the requirements, i noticed that what you really need, in such scenario, is two results. One for Specification = 1 and another for Specification = 2. Thus you need two formulas, one in E2 and another in E3.

So let me try this new and shorter version

	A​	B​	C​	D​	E​	F​
1​	SPECIFICATION #​	VALUES​		SPECIFICATION #​	RESULT​	EXCLUSION LIST​
2​	1​	COMP EVAL​		1​	COMP EVAL​	P5​
3​	1​	P5​		2​	N/A​	P1​
4​	1​	P2​		3​	PROTOTYPE​	P3​
5​	2​	P4​		4​	P2​	P4​
6​	2​	P4​		5​	COMP EVAL​
7​	2​	P3​
8​	3​	PROTOTYPE​
9​	3​	P5​
10​	3​	P1​
11​	4​	P3​
12​	4​	P2​
13​	4​	P1​
14​	5​	COMP EVAL​
15​	5​	P2​
16​	5​	P1​

<TBODY>
</TBODY>

Array formula in E2 copied down
=IF(D2<>"",IFERROR(INDEX($B$2:$B$16,MATCH(SMALL(IF(A$2:$A$16=$D2,IF($B$2:$B$16<>"",IF(ISNA(MATCH($B$2:$B$16,$F$2:$F$16,0)),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16)))),1),COUNTIF($B$2:$B$16,"<"&$B$2:$B$16),0)),"N/A"),"")

Ctrl+Shift+Enter

Hope this helps

M.