I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000

One way (Array-entered: CTRL-SHIFT-ENTER or cmd-RETURN):

=IF(C2=MAX(($A$2:$A$100=A2)*$C$2:$C$100),"Max","")

copy down.

Better (but not one of your options):

A Pivot Table with Unit in the Row field and Max of ODM in the Data
field.

In article ,
Erika wrote:

I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000

One way ..

Put in D2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=AND(C2=MAX(IF(A$2:A$100=A2,C$2:C$100)),C2<"")
Copy D2 down, then autofilter on col D for TRUE.
Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Erika" wrote:
I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000

On Oct 6, 12:06 am, Erika wrote:
I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000

=IF(SUMPRODUCT(MAX(($C$2:$C$100)*($A$2:$A$100=A2)) )=C2,C2,"")

Ken Johnson

Erika,
Another idea. If you want the maximum values to be highlighted, use the
formulas suggested by various respondents to your question and get column C
conditionally formatted. Only the maximum values will be highlighted. (
Format Conditional formatting -- in the dialogue box that pops up select
"Formula is " and copy the formula you like and indicate the formatting you
want by pressing format button and selecting a color your choice.

"Erika" wrote:

I have a sample of the spreadsheet that I am working with below, what I need
to do is put together a function or formula that will flag the column with
the highest ODM reading for each unit. Any ideas on a way that I could do
that - once the row was flagged on could filter to get the report that I want
- each unit listed with only the highest ODM.

unit Miles ODM
1 10 50000
1 20 40000
3 100 10
3 150 150
3 200 250000