MIN / MAX for specific value from other column

[Guest]

I tried before, and thanks Peo Sjoblom, but I still do not get what I need

The data I work with starts in Row7 of all Columns, Row1:6 is for Info etc

In Column (C7:C156), I have Registration numbers of vehicles as they fill up each day.
They are NOT sorted (non-alphabetical). They are listed as the days go by for the month. (For daily records

In Column (D7156), I have the odometer(speedo) readings of the vehicles (opposite the Reg. No. in column C.)

In Column AB (AB7:AB73), I have all of the companies vehicle Reg. No's, whether they used fuel or not during the month, sorted in alphanumeric order.

What I now need, is to get the (MIN) speedo reading for each specific Reg. No.(vehicle), and the (MAX) for the same Reg. No. in the AF & AG COLUMNS.

Thus, AF7 will look up the Reg. No. in AB7, and then scroll through C7:C156 to find all entries for that specific Reg. No. , then look in D7156 for the kilos and write the MIN kilos in Column AF7.
Same for MAX.
NOTE: sometimes there is no value in some rows of D7156 (thus zero kilometers)

MIN & MAX formulas by themselves do not do the trick, maybe I have to nest formulas somehow, I would appreciate it if someone could help

Regards
Theun

 

[Steve Smallman]

A complete answer would involve coding a user defined
function, and while relatively simple, may be confusing.

Have a look at DMIN and DMAX and see if they can work for
you, as they may do what you need. Otherwise, email me
and I will give you a hand to do the UDF, or re post and
someone here will give you a start.


Steve

-----Original Message-----
I tried before, and thanks Peo Sjoblom, but I still do not get what I need.

The data I work with starts in Row7 of all Columns, Row1:6 is for Info etc.

In Column (C7:C156), I have Registration numbers of

vehicles as they fill up each day.

They are NOT sorted (non-alphabetical). They are listed

as the days go by for the month. (For daily records)

In Column (D7156), I have the odometer(speedo)

readings of the vehicles (opposite the Reg. No. in
column C.)

In Column AB (AB7:AB73), I have all of the companies

vehicle Reg. No's, whether they used fuel or not during
the month, sorted in alphanumeric order.

What I now need, is to get the (MIN) speedo reading for

each specific Reg. No.(vehicle), and the (MAX) for the
same Reg. No. in the AF & AG COLUMNS.

Thus, AF7 will look up the Reg. No. in AB7, and then

scroll through C7:C156 to find all entries for that
specific Reg. No. , then look in D7156 for the kilos
and write the MIN kilos in Column AF7.

Same for MAX.
NOTE: sometimes there is no value in some rows of D7156 (thus zero kilometers)

MIN & MAX formulas by themselves do not do the trick,

maybe I have to nest formulas somehow, I would appreciate
it if someone could help.

 

[Leo Heuser]

Theuns

One way:

In AF7 enter this array formula:

=MIN(IF(($C$7:$C$156=AB7)*($D$7:$D$156<>""),$D$7:$D$156))

The formula must be array entered, i.e. with <Shift><Ctrl><Enter>
instead of just <Enter>, also if edited later.

In AG7 enter this array formula:

=MAX(($C$7:$C$156=AB7)*$D$7:$D$156)

also array entered.

Select AF7:AG7 and copy the selection down with the fill handle
(the little square in the lower right corner of the selection.

I'm not sure, what you mean by:

NOTE: sometimes there is no value in some rows of D7156 (thus zero

kilometers)

If you want the MIN to count as zero kilometers in
these instances change the MIN formula to:

=MIN(($C$7:$C$156=AB7)*$D$7:$D$156)

Still array entered.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

I tried before, and thanks Peo Sjoblom, but I still do not get what I need.

The data I work with starts in Row7 of all Columns, Row1:6 is for Info etc.

In Column (C7:C156), I have Registration numbers of vehicles as they fill up each day.
They are NOT sorted (non-alphabetical). They are listed as the days go by

for the month. (For daily records)

In Column (D7156), I have the odometer(speedo) readings of the vehicles

(opposite the Reg. No. in column C.)

In Column AB (AB7:AB73), I have all of the companies vehicle Reg. No's,

whether they used fuel or not during the month, sorted in alphanumeric
order.

What I now need, is to get the (MIN) speedo reading for each specific Reg.

No.(vehicle), and the (MAX) for the same Reg. No. in the AF & AG COLUMNS.

Thus, AF7 will look up the Reg. No. in AB7, and then scroll through

C7:C156 to find all entries for that specific Reg. No. , then look in
D7156 for the kilos and write the MIN kilos in Column AF7.

Same for MAX.
NOTE: sometimes there is no value in some rows of D7156 (thus zero kilometers)

MIN & MAX formulas by themselves do not do the trick, maybe I have to nest

formulas somehow, I would appreciate it if someone could help.