Finding MIN text value from array excluding certain specified values

L

logonwheeler

New Member
Joined
Feb 1, 2014
Messages
17
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? :confused:

Data Set:
1 A
2 D
3 A
4 C
5 B
6 D
7 C
8 B
9 A
 
Sort by date Sort by votes
Actually, the formula I offered should also return the desired result in circumstances such as the following...

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Values
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Result
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Exclusion List
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P3-2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"]P5
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]COMP EVAL
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]P2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]P1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P3-2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]P4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PROTOTYPE
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Try this:

Code: 
Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz
 
Upvote 0
markmzz said:
Try this:

Code: 
Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz
Click to expand...

It doesn't look like it returns the desired result if the range contains an empty cell, or if the range contains only values from the exclusion list.
 
Upvote 0
Domenic said:
It doesn't look like it returns the desired result if the range contains an empty cell, or if the range contains only values from the exclusion list.
Click to expand...

Domenic,

You can try this:

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

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

Anyway, let's wait for the user.

Markmzz
 
Upvote 0
Domenic said:
Assuming that A2:A10 contains the data, and C2:C5 contains the exclusion list, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

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

Hope this helps!
Click to expand...

I can confirm that this solution works. Thanks Domenic for your contribution.
 
Upvote 0
Marcelo Branco said:
Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
VALUES​
[/TD]
[TD]
RESULT​
[/TD]
[TD]
EXCLUSION LIST​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
COMP EVAL​
[/TD]
[TD]
1​
[/TD]
[TD]
P5​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
COMP EVAL​
[/TD]
[TD][/TD]
[TD]
COMP EVAL​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
P5​
[/TD]
[TD][/TD]
[TD]
P2​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
P4​
[/TD]
[TD][/TD]
[TD]
P1​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
P3-2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
P2​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
P1​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
PROTOTYPE​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B2
=INDEX($A$2:$A$10,MATCH(MIN(IF(ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0)),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10&" "))),COUNTIF($A$2:$A$10,"<"&$A$2:$A$10&" "),0))

Ctrl+Shift+Enter

M.
Click to expand...

Marcelo - I can confirm that this solution works as well as Domenic's solution. Thanks also for your contribution.
 
Upvote 0
markmzz said:
Try this:

Code: 
Use Ctrl+Shift+Enter and not just Enter to enter the formula

=INDEX($A$2:$A$10,MATCH(
MIN(MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0)*ISNA(MATCH($A$2:$A$10,$C$2:$C$5,0))),
MMULT(-($A$2:$A$10&"" < TRANSPOSE($A$2:$A$10&"")),ROW($A$2:$A$10)^0),0))

Markmzz
Click to expand...

Marknmzz - thanks for offering this solution. As others have mentioned, if any of the "VALUES" cells are empty or blank, this solution will return a result of "0" instead of returning the min from the remainder of the data set.
 
Upvote 0
markmzz said:
Domenic,

You can try this:

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

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

Anyway, let's wait for the user.

Markmzz
Click to expand...

Markmzz - Thanks for this solution - it also works similarly to the other two solutions previously posted. Thanks for your contribution to this topic.
 
Upvote 0
Thanks to everyone who has contributed to this topic thus far. Many creative solutions that are working quite well. You should pat yourselves on the back.

I have what I believe one final use case & solution request.

I would like to take this solution one step further. I would like to do the same find MIN value of alphanumeric value from a data set as before, but this time reference a "SPECIFICATION #" value in which to lookup prior to returning a value.

In other words - the user inputs a value for a SPECIFICATION # in cell D2, and the calculated value for E2 is the MIN alphanumeric value for all SPECIFICATION #s matching the value in D2. In the use case below, the user inputs "4" for SPECIFICATION #. The calculated value for E2 is P1 because for all SPECIFICATION # values equal to "4" (P3, P2, P1) the MIN value of those returned is P2 because both P3 and P1 are on the exclusion list.

Data Set:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SPECIFICATION #[/TD]
[TD]VALUES[/TD]
[TD][/TD]
[TD]SPECIFICATION #[/TD]
[TD]RESULT[/TD]
[TD]EXCLUSION LIST[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]COMP EVAL[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]P2[/TD]
[TD]P5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COMP EVAL[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]PROTOTYPE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5[/TD]
[TD]COMP EVAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]5[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Who is up for this challenge? Thanks in advance for your time spent looking into this challenge. :cool:
 
Upvote 0
logonwheeler said:
Thanks to everyone who has contributed to this topic thus far. Many creative solutions that are working quite well. You should pat yourselves on the back.

I have what I believe one final use case & solution request.

I would like to take this solution one step further. I would like to do the same find MIN value of alphanumeric value from a data set as before, but this time reference a "SPECIFICATION #" value in which to lookup prior to returning a value.

In other words - the user inputs a value for a SPECIFICATION # in cell D2, and the calculated value for E2 is the MIN alphanumeric value for all SPECIFICATION #s matching the value in D2. In the use case below, the user inputs "4" for SPECIFICATION #. The calculated value for E2 is P1 because for all SPECIFICATION # values equal to "4" (P3, P2, P1) the MIN value of those returned is P2 because both P3 and P1 are on the exclusion list.

Data Set:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SPECIFICATION #[/TD]
[TD]VALUES[/TD]
[TD][/TD]
[TD]SPECIFICATION #[/TD]
[TD]RESULT[/TD]
[TD]EXCLUSION LIST[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]COMP EVAL[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]P2[/TD]
[TD]P5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COMP EVAL[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]P3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]P4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2[/TD]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3[/TD]
[TD]PROTOTYPE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[TD]P5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]P3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5[/TD]
[TD]COMP EVAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]5[/TD]
[TD]P1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Who is up for this challenge? Thanks in advance for your time spent looking into this challenge. :cool:
Click to expand...

Just plug in the required term A2:A5=D2 in the formula Domenic proposed:
Rich (BB code): 
=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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
VBA: Fill Array2 from Array1 with >= Values
Replies
7
Views
243
zero269
Z
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
226
Alex Blakenburg
A
C
  • Solved
I need a formula to a) find the first numeral in a text string b) remove that numeral and all the characters after that numeral
Replies
3
Views
101
Peter_SSs
Peter_SSs
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
478
acool
A
M
  • Solved
Filter excluded rows in array formula
Replies
5
Views
90
Eric W
Eric W

Forum statistics

Threads
1,226,797
Messages
6,193,055
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top