Help with finding max value in an alphanumeric string

Number1

Board Regular
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.

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Book5
ABCD
1DIO
22071
34069
44071
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).

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

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

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

Replies
0
Views
174
Replies
8
Views
168
Replies
2
Views
373
Replies
6
Views
286
Replies
5
Views
124

1,203,674
Messages
6,056,682
Members
444,882
Latest member
cboyce44

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.

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

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