| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
From range and criteria select highest value
Hello from Sunny RSA,
I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Source data is assumed in A1:B6
Values listed in D1 down In E1, array-entered (press CTRL+SHIFT+ENTER): =MAX(IF(A$1:A$6=D1,B$1:B$6)) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sunnyskies" wrote: Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
With your DB in the range A2:B7...
Unique unit codes in the range E2:E4... Enter this array formula** in F2 and copy down to F4: =MAX(IF(A$2:A$7=E2,B$2:B$7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Hi TV,
Can this not be done without an aray? Other users need to use the document and they are not savy. "T. Valko" wrote: With your DB in the range A2:B7... Unique unit codes in the range E2:E4... Enter this array formula** in F2 and copy down to F4: =MAX(IF(A$2:A$7=E2,B$2:B$7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Can this not be done without an aray?
An alternative is to create a pivot table, with col A's header placed in ROW and col B's header in DATA (set to summarize by Max). The pivot will produce both the unique listing of items (from col A) and the corresponding maximums adjacent to it -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Hi Max,
Thought about that as well, but I want to keep the file size as small as possible. So was hoping for a formula. Thanks "Max" wrote: Can this not be done without an aray? An alternative is to create a pivot table, with col A's header placed in ROW and col B's header in DATA (set to summarize by Max). The pivot will produce both the unique listing of items (from col A) and the corresponding maximums adjacent to it -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Can this not be done without an aray?
Try this: =SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7)) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hi TV, Can this not be done without an aray? Other users need to use the document and they are not savy. "T. Valko" wrote: With your DB in the range A2:B7... Unique unit codes in the range E2:E4... Enter this array formula** in F2 and copy down to F4: =MAX(IF(A$2:A$7=E2,B$2:B$7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
Hi TV,
Excellent. Suggest you give yourself a pat on the back. Thanks "T. Valko" wrote: Can this not be done without an aray? Try this: =SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7)) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hi TV, Can this not be done without an aray? Other users need to use the document and they are not savy. "T. Valko" wrote: With your DB in the range A2:B7... Unique unit codes in the range E2:E4... Enter this array formula** in F2 and copy down to F4: =MAX(IF(A$2:A$7=E2,B$2:B$7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
From range and criteria select highest value
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hi TV, Excellent. Suggest you give yourself a pat on the back. Thanks "T. Valko" wrote: Can this not be done without an aray? Try this: =SUMPRODUCT(MAX((A$2:A$7=E2)*B$2:B$7)) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hi TV, Can this not be done without an aray? Other users need to use the document and they are not savy. "T. Valko" wrote: With your DB in the range A2:B7... Unique unit codes in the range E2:E4... Enter this array formula** in F2 and copy down to F4: =MAX(IF(A$2:A$7=E2,B$2:B$7)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sunnyskies" wrote in message ... Hello from Sunny RSA, I have the following database: Unit Kilometers 98R01 109145 06R02 134873 06R02 129523 98R01 110987 07T03 98654 07T03 101345 Now I need a formula that will go and pick the highest kilometers to populiate the following table: 98R01 06R02 07T03 The result should be 98R01 110987 06R02 134873 07T03 101345 The unit numbers are unique, there is no between themselves. I think MAX needs be used, but I am not sure how to apply this to a criteria. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto select highest 25 scores. | Excel Worksheet Functions | |||
| select highest value | Excel Worksheet Functions | |||
| How-to automatically select and chart the highest 10 totals? | Excel Discussion (Misc queries) | |||
| date criteria to select range | Excel Worksheet Functions | |||
| "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Linear Mode
