Finding a MAX and MIN value with criteria within a ALPHA-Numeric cloumnm

[JChaney17]

I am looking for a couple of formulas that will return a MAX and MIN value from a Alpha-numeric column with criteria. This is what I am looking at:

[TABLE="width: 221"]
<TBODY>[TR]
[TD]No.</SPAN>[/TD]
[TD]Tag No</SPAN>[/TD]
[TD]Type</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]A001678</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]A001679</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]A001680</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]A001681</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]A001682</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]A001683</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]A001684</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]A001685</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]A001686</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]A001687</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]A001688</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]A001689</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]A001690</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]A001691</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]A001692</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD]A001693</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]A001694</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]18</SPAN>[/TD]
[TD]A001695</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]19</SPAN>[/TD]
[TD]A001696</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
[TR]
[TD]20</SPAN>[/TD]
[TD]A001697</SPAN>[/TD]
[TD]S</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

I am wanting to look for "S" in the "TYPE" column, and then have MAX and MIN formula's look for the MAX and MIN values in the "Tag No" column. I can get the number to display, but I would like to have the MAX and MIN values displayed as "A00####".

Any help would be appreciated.

Thanks.

 

[Firefly2012]

Hi

One formula option:

Excel 2007
	A	B	C	D	E	F	G	H
1	No.	Tag No	Type			S	P	E
2	1	A001678	P		Max	A001697	A001691	A001693
3	2	A001679	S		Min	A001679	A001678	A001682
4	3	A001680	S
5	4	A001681	P
6	5	A001682	E
7	6	A001683	E
8	7	A001684	S
9	8	A001685	S
10	9	A001686	S
11	10	A001687	S
12	11	A001688	S
13	12	A001689	P
14	13	A001690	P
15	14	A001691	P
16	15	A001692	E
17	16	A001693	E
18	17	A001694	S
19	18	A001695	S
20	19	A001696	S
21	20	A001697	S
Sheet3

Cell Formulas
Range	Formula
F2	{=TEXT(MAX(IF($C$2:$C$21=F$1,MID($B$2:$B$21,2,255)+0)),"\A000000")}
F3	{=TEXT(MIN(IF($C$2:$C$21=F$1,MID($B$2:$B$21,2,255)+0)),"\A000000")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JChaney17]

Thanks...that worked great. Since I am not real familiar with these formula's, can you tell me what the MID and the "2,255" are doing?

 

[Firefly2012]

The MID is used to remove the "A" from the value (leaving just numeric digits):

MID(A1,2,255)

Says take all characters from the second character (255 used because it will always be longer than the actual Tag No so will always capture the remaining characters).

 

[JChaney17]

Thanks again for the help and explanation.