latest date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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)
 
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
 
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 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
Click to expand...
 
Back
Top