Finding MIN text value from array excluding certain specified values

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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
hi & welcome

One way is with a query table with SQL like

Code:
SELECT DISTINCT TOP 1 YourFieldName
FROM [YourSheetName$]
WHERE YourFieldName Not In ('A', 'D')
ORDER BY YourFieldName

change the worksheet name and your field name to suit. One way to set it up is save the file & then go ALT-D-D-N & follow the wizard. If you get a message about no visible tables you can OK to acknowledge that and then via options select system tables to see worksheet names. Follow the wizard to the end & choose to edit in MS Query. Then via the SQL icon change the SQL to above, OK to enter it & see the results dataset, 'open door' icon to exit MS Query & load result to a worksheet.

regards
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Is this really about letters?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Yes - the numbers are just for rows 1-9. Sorry for any confusion.

Control+shift+enter, not just enter:
Rich (BB code):
=CHAR(MIN(IF(1-ISNUMBER(MATCH($A$1:$A$9,{"A","D"},0)),
  CODE($A$1:$A$9),9.99999999999999E+307)))
 

logonwheeler

New Member
Joined
Feb 1, 2014
Messages
17
Control+shift+enter, not just enter:
Rich (BB code):
=CHAR(MIN(IF(1-ISNUMBER(MATCH($A$1:$A$9,{"A","D"},0)),
  CODE($A$1:$A$9),9.99999999999999E+307)))

Aladin,

Works really great. Thanks for that formula.

Taking this a step further, how do we return more than the just the 1st character of the cell? I would like to return the entire the contents of the alphanumeric cell contents, not just the 1st character of the returned cell.

For example, if my data set contains more than one character:

1 COMP EVAL
2 COMP EVAL
3 P5
4 P4
5 P3
6 P3-2
7 P2
8 P1
9 PROTOTYPE

Rich (BB code):
=CHAR(MIN(IF(1-ISNUMBER(MATCH($A$1:$A$9,{"P5","P4"},0)),
  CODE($A$1:$A$9),9.99999999999999E+307)))
Returns the result: P



However I would like the following result:
Rich (BB code):
"ENTER CODE HERE"
Returns the result: COMP EVAL



The formula should be flexible to return a larger (from 1 to no more than approx 40) set of characters in the result. Any ideas?
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

Hi,

Your desired results do not seem to have transferred correctly in your last post.

Can you just clarify: are you looking to exclude cell entries which are precisely "A" or "D" from your alphabetically-sorted returns? Or cell entries which contain anywhere within their string the (case-sensitive?) letters "A" or "D"?

If the latter, and assuming you are using Excel 2007 or later, perhaps this array formula**:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(1-MMULT(--(ISNUMBER(FIND({"A","D"},$A$1:$A$9))),{1;1}),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Copy down as required.

Regards



**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
</SPAN>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Aladin,

Works really great. Thanks for that formula.

Taking this a step further, how do we return more than the just the 1st character of the cell? I would like to return the entire the contents of the alphanumeric cell contents, not just the 1st character of the returned cell.

For example, if my data set contains more than one character:

1 COMP EVAL
2 COMP EVAL
3 P5
4 P4
5 P3
6 P3-2
7 P2
8 P1
9 PROTOTYPE

Rich (BB code):
=CHAR(MIN(IF(1-ISNUMBER(MATCH($A$1:$A$9,{"P5","P4"},0)),
  CODE($A$1:$A$9),9.99999999999999E+307)))
Returns the result: P



However I would like the following result:
Rich (BB code):
"ENTER CODE HERE"
Returns the result: COMP EVAL



The formula should be flexible to return a larger (from 1 to no more than approx 40) set of characters in the result. Any ideas?

I should have said chars, not letters. The previous formula is meant to work with chars.

But, try the following:

Control+shift+enter, not just enter:
Rich (BB code):
=INDEX($A$1:$A$9,MATCH(0,IF(ISNA(MATCH(A1:A9,B1:B2,0)),
  COUNTIF(A1:A9,"<"&A1:A9),9.99E+307),0))
where B1:B2 houses the strings to exclude.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Apologies. Noticed an error in my formula. Should be:

=IFERROR(INDEX($A$1:$A$9,MATCH(SMALL(IF(MMULT(--(ISNUMBER(FIND({"A","D"},$A$1:$A$9))),{1;1})=0,COUNTIF($A$1:$A$9,"<"&$A$1:$A$9)),ROWS($1:1)),COUNTIF($A$1:$A$9,"<"&$A$1:$A$9),0)),"")

Regards
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I should have said chars, not letters. The previous formula is meant to work with chars.

But, try the following:

Control+shift+enter, not just enter:
Rich (BB code):
=INDEX($A$1:$A$9,MATCH(0,IF(ISNA(MATCH(A1:A9,B1:B2,0)),
  COUNTIF(A1:A9,"<"&A1:A9),9.99E+307),0))
where B1:B2 houses the strings to exclude.

This won't work at all when the value it calculates as the Min value is part of the exclude list, as Marcelo Branco pointed out to me in a PM.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,027
Members
414,356
Latest member
death20

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
Top