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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
 
Upvote 0
logonwheeler said:
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
Click to expand...

Is this really about letters?
 
Upvote 0
Aladin Akyurek said:
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)))
Click to expand...

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?
 
Upvote 0
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>
 
Upvote 0
logonwheeler said:
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?
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
Aladin Akyurek said:
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Lowest value in range of cells excluding this row
2
Replies
10
Views
212
Peter_SSs
Peter_SSs
S
  • Solved
Lookup using IF/Match returning wrong values
Replies
2
Views
86
sundodger
S
D
  • Question
Exclusion of value using SMALL IF
Replies
5
Views
240
Fluff
Fluff
D
  • Solved
Vlookup/Index that returns all distinct values, but returns in a row?
Replies
8
Views
361
Fluff
Fluff
B
  • Question
Excluding a cell from MINIFS calculation - error when excluded cell is equal to first cell in range
Replies
2
Views
224
FormR
FormR

Forum statistics

Threads
1,214,388
Messages
6,119,227
Members
448,878
Latest member
Da9l87

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