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

 

[markmzz]

Markmzz - Thanks for this solution - it also works similarly to the other two solutions previously posted. Thanks for your contribution to this topic.

You are welcome.

And try this for your new problem:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(MATCH(IF($A$2:$A$16=$D$2,$B$2:$B$16),$F$2:$F$5,0))=SUM(($B$2:$B$16<>"")*($A$2:$A$16=$D$2)),"",INDEX($B$2:$B$16,MATCH(
MIN(MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0)*($B$2:$B$16<>"")*($A$2:$A$16=$D$2)*ISNA(MATCH($B$2:$B$16,$F$2:$F$5,0))),
MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0),0)))

Markmzz

 

[logonwheeler]

Just plug in the required term A2:A5=D2 in the formula Domenic proposed:

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

Aladin,

For some reason, I am not able to get this to work with the data set that I presented. Here are a few things that I picked up with your code you provided.

A2:A15 should be A2:A16
B2:B15 should be B2:B16

C2:C5 should be F2:F5 (please confirm)

Here is a copy/paste of the code with these updates:

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

But still not working. Any ideas why?

 

[logonwheeler]

You are welcome.

And try this for your new problem:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(MATCH(IF($A$2:$A$16=$D$2,$B$2:$B$16),$F$2:$F$5,0))=SUM(($B$2:$B$16<>"")*($A$2:$A$16=$D$2)),"",INDEX($B$2:$B$16,MATCH(
MIN(MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0)*($B$2:$B$16<>"")*($A$2:$A$16=$D$2)*ISNA(MATCH($B$2:$B$16,$F$2:$F$5,0))),
MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0),0)))

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.

 

[Aladin Akyurek]

Aladin,

For some reason, I am not able to get this to work with the data set that I presented. Here are a few things that I picked up with your code you provided.

A2:A15 should be A2:A16
B2:B15 should be B2:B16

C2:C5 should be F2:F5 (please confirm)

Here is a copy/paste of the code with these updates:

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

But still not working. Any ideas why?

I get P2...

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

 

[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.

Here all is ok. Look at this:

Layout

SPECIFICATION #	VALUES		SPECIFICATION #	RESULT	EXCLUSION LIST
1	COMP EVAL		1	COMP EVAL
1	P5		2	P3
1	P2		3	P1
2	P4		4	P1
2	P4		5	COMP EVAL
2	P3
3	PROTOTYPE
3	P5
3	P1
4	P3
4	P2
4	P1
5	COMP EVAL
5	P2
5	P1
***************	**********	**	***************	************	**************

<tbody>
</tbody>

Formula

In E2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(COUNT(MATCH(IF($A$2:$A$16=$D2,$B$2:$B$16),$F$2:$F$5,0))=SUM(($B$2:$B$16<>"")*($A$2:$A$16=$D2)),"",INDEX($B$2:$B$16,MATCH(
MIN(MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0)*($B$2:$B$16<>"")*($A$2:$A$16=$D2)*ISNA(MATCH($B$2:$B$16,$F$2:$F$5,0))),
MMULT(-($B$2:$B$16&"" < TRANSPOSE($B$2:$B$16&"")),ROW($B$2:$B$16)^0),0)))

And copy down until E6.

Markmzz

 

[Marcelo Branco]

Maybe...
(assumes all values are alphanumeric or numeric inserted as text)

=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 #]

 

[markmzz]

Markmzz,

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.

In this case, do some tests with this:

Layout

SPECIFICATION #	VALUES		SPECIFICATION #	RESULT	EXCLUSION LIST
1	COMP EVAL		1	P2	P5
5	COMP EVAL		2	P4	COMP EVAL
3	P1		3	PROTOTYPE	P1
4	P1		4	P2	P3
5	P1		5	P2
1	P2
4	P2
5	P2
2	P3
4	P3
2	P4
2	P5
1	P5
3	P5
3	PROTOTYPE
***************	**********	**	***************	************	**************

<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <tbody> </tbody>

Formula

In E2

=IFERROR(INDEX($B$2:$B$16,MATCH(1,INDEX(($B$2:$B$16<>"")*($A$2:$A$16=$D2)*ISNA(MATCH($B$2:$B$16,$F$2:$F$5,0)),),0)),"")

And copy down.

Markmzz

 

[logonwheeler]

I get P2...

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

Aladin - If I change the SPECIFICATION # from 1 to 2 to 3 and so on, the result does not calculate properly. Can you confirm?

For example, if I change the SPECIFICATION # to 3, the result is P2 which is not even a possibility.

 

[logonwheeler]

Maybe...
(assumes all values are alphanumeric or numeric inserted as text)

=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.

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?