Show only latest Date of Service?

[Guest]

Each customer (column A) on my list has multiple dates of service(column B).
How can I sort to only show the most recent date of service?

 

[Guest]

If you sort descending on dates (real numeric dates) it will come up first,
if you meant you want a formula to retrun the latest date

=INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))

will give the customer with the latest date

Regards,

Peo Sjoblom

 

[Guest]

I am looking for the latest date of service for EACH customer. The sort
feature does not work because it may return multiple dates of service for
client A before listing the latest date of service for client B.

 

[CLR]

You can set up your name and date list as a VLOOKUP table, and sort it
decending on the date column, then assuming your list was in A and B, you
could type a name in C1 and put this in D1

=VLOOKUP(C1,NameDateRange,2,FALSE)

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

Each customer (column A) on my list has multiple dates of service(column B).
How can I sort to only show the most recent date of service?

Two methods.

1. Use a Pivot table. Drag the Customers to the ROW; drag the dates of
service to the DATA area and select MAX for the function (right click on the
Data field, select Field Settings, and then MAX).

2. a. Data/Sort: First by Column A (ascending) and then by Column B.

Then

b. Data/Subtotals: For each change in Customers, use the MAX function.

c. In the extreme left margin, you will see some very small numbers 1, 2 and
3. If you click on the 2, this should hide all except the subtotals.


--ron

 

[Frank Kabel]

Hi
then try using a pivot table for this

 

[Guest]

Ron as a follow-up:



I have never used a pivot table so I am trying suggestion #2 first:

I am able to do the data sort, but am not sure how to do the “For each
change in Customers, use the MAX function". How would I dinstinguish the
change in customer? Could you give me an example formula? Thanks

 

[Ron Rosenfeld]

I have never used a pivot table so I am trying suggestion #2 first:

I am able to do the data sort, but am not sure how to do the “For each
change in Customers, use the MAX function". How would I dinstinguish the
change in customer? Could you give me an example formula? Thanks

"Data/Subtotals" as I wrote in my previous post is a shortcut notation. It
means to select the Data menu from the top line in Excel; and then select
Subtotals from the List that drops down (just as you did in order to get to the
Sort option).

Once you do that, a dialog box will open with the options I mentioned.


--ron