Find largest value in a column depending on value in another

[LoriD]

I have 2 sheets. "EquipmentTable" and "MaintRecords". (Each time a piece of equipment is serviced, it is entered into MaintRecords.) In my "Equipment table", all equipment is listed. I want to pull in the current hours from the "MaintRecords" sheet for each unit, into the EquipmentTable, but don't know how. I've been trying to combine the DMAX with VLOOKUP to get this, but I really am not sure if it's even possible. Can anyone tell me how to do this? Thanks.

 

[Dave Patton]

On 2002-09-24 07:53, LoriD wrote:
I have 2 sheets. "EquipmentTable" and "MaintRecords". (Each time a piece of equipment is serviced, it is entered into MaintRecords.) In my "Equipment table", all equipment is listed. I want to pull in the current hours from the "MaintRecords" sheet for each unit, into the EquipmentTable, but don't know how. I've been trying to combine the DMAX with VLOOKUP to get this, but I really am not sure if it's even possible. Can anyone tell me how to do this? Thanks.

Could you describe a very concise example.
- do you have to calculate the max
- do you have to total information
- what columns have descriptions and amounts
-

 

[LoriD]

No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

 

[Aladin Akyurek]

On 2002-09-24 11:18, LoriD wrote:
No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

(1.) Name the range LTable where the lookup table is.
(2.) Now use:

=SUMPRODUCT(MAX((INDEX(LTable,0,1)=lookup-value)*(INDEX(LTable,0,10))))

for Miles. Change 10 to 11 for Hours.


EDIT: Reminded by Dave's post, jusr drop superfluous SUMPRODUCT and just array-enter:

=MAX((INDEX(LTable,0,1)=lookup-value)*(INDEX(LTable,0,10)))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
This message was edited by aladin akyurek on 2002-09-24 12:52

 

[Dave Patton]

On 2002-09-24 11:18, LoriD wrote:
No, I don't need totals, and I don't think I need to calculate MAX, I just want to bring over from the MaintRecords sheet, whatever the largest value is in the column for a given piece of equipment.
Sheet "EquipmentTable" lists Unit#, Serial#,Make,Model,etc. ColumnA holds the unit number. So if cell A1 is Unit# WY1 I want to display in columnF, the current Hours on the equipment, and in columnG, the current Miles. Those would come from the MaintRecords sheet. Unit #WY1 will be listed many times, once for each time it is serviced. At each service, the current miles and hours are recorded in columns J & K. So I want to look up the largest mileage value in columnJ and hour value in columnK for Unit WY1, and have it on the EquipmentTable worksheet. I hope I haven't confused the issue here!! Thanks again.

with criteria in a cell ( I used A50)

array enter with Ctrl-Shift-Enter

=MAX(IF(MaintRecords!A2:A100=A50,MaintRecords!J2:J100))

Revise the references to fit your information.


You could try a Pivot Table.

 

[LoriD]

Thank you!

 

[LoriD]

Thank you!

 

[Dave Patton]

You may still want to try Pivot Tables
Set data to MAx not Sum

You can still access particular numbers via
formula.

Max of Miles
Unit Total
Unit# WY1 520
XYZ 1500


unit name in A11 and formula below in B11

Miles
Unit# WY1 520

B11 =GETPIVOTDATA(A3,""&A11)