Help with finding max value in an alphanumeric string

[Number1]

HELP!!!

I have two sheets in a workbook. Column A in both workbooks has one of the three following letters: O, D or I. Column B in both workbooks has numbers. What I am looking to do is find the max number for each corresponding O, D, and I across both of sheets. Said another way...If you were to combine the Column A with the corresponding Column B number to get O1 or O2 or D1 or D2 or I1 or I3 etc. I want the max number for O, max for D and Max for I. I want to avoid using a macro for this an use built in functions because I want it to be automatic when an O, D or I are added with its corresponding number. I can not count on each column being sorted any particular way. Example

Sheet 1
Col A Col B
O 18
O 50
O 68
O 71
D 2
O 36
O 49

Sheet 2
Col A Col B
O 51
O 52
O 57
O 58
O 69
D 1
D 3
D 4

Then the formula I am looking for would return 71 for the max O and 4 for the max D etc.

 

[Aladin Akyurek]

Book5
	A	B	C	D
1	D	I	O
2	2	0	71
3	4	0	69
4	4	0	71
5
Sheet3

A formula like

=MAX(IF(Sheet1!$A$1:$A$7=A1,Sheet1!$B$1:$B$7))

is an array formula, which must be entered using control+shift+enter (not just enter).

 

[IML]

There may be a way to consolidate this, but you could array enter (hit enter with control and shift depressed)
=MAX(MAX(IF(Sheet1!$A$1:$A$7="O",Sheet1!$B$1:$B$7),MAX(IF(Sheet2!$A$1:$A$8="O",Sheet2!$B$1:$B$8))))

Good luck

 

[Aladin Akyurek]

You might also consider using DMAX instead of array formulas.
This message was edited by aladin akyurek on 2002-09-13 13:09

 

[Number1]

Thanks to IML and Aladin - looks like both of you had the same idea and it worked!