latest date

[hitesh]

help need

hi
i have a list of disconnection customer. in that list the disconnection date
is repeated. means disconnection is made more than 2 time. it is difficult to
know the latest date of disconnection so pls help to
the latest date of disconnection of particular customer

 

[Bob Phillips]

=MAX(IF(B1:B100="customer",C1:C100))

where B is the customer name, C the date

It is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Why not apply autofilter to select the customer name and then examine
the dates for that customer?

Alternatively, you could use an array* formula like:

=MAX(IF(customer_range=X1,date_range))

where customer_range covers the cells where your customers are
located, eg A1:A100, date_range is where the dates are, eg F1:F100,
and X1 is used for the name of the customer you are interested in.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you need to use CTRL-SHIFT-ENTER instead of the
usual ENTER to commit it. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself.

Hope this helps.

Pete

 

[hitesh]

when i enter the customer code date does not appear. i have to say that if i
enter a customer code in sheet 1 cell A2, the t customer name should appear
in cell B2 and latest date in C2. is it possible. my data base is in sheet 1.

regards

 

[bu alemın krali]

bu metın yarak larda dırt


"Pete_UK":

Why not apply autofilter to select the customer name and then examine
the dates for that customer?

Alternatively, you could use an array* formula like:

=MAX(IF(customer_range=X1,date_range))

where customer_range covers the cells where your customers are
located, eg A1:A100, date_range is where the dates are, eg F1:F100,
and X1 is used for the name of the customer you are interested in.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you need to use CTRL-SHIFT-ENTER instead of the
usual ENTER to commit it. If you do this correctly then Excel will
wrap curly braces { } around the formula when viewed in the formula
bar - do not type these yourself.

Hope this helps.

Pete

 

[Pete_UK]

In English, please?

Pete

bu met n yarak larda d rt

"Pete_UK":







- Show quoted text -