Show only latest Date of Service?
 

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?

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

"Woodkat" wrote:

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?

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.

"Peo Sjoblom" wrote:

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

"Woodkat" wrote:

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?

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



"Woodkat" wrote in message
...
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?

On Wed, 8 Dec 2004 15:23:01 -0800, "Woodkat"
wrote:

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

Hi
then try using a pivot table for this

--
Regards
Frank Kabel
Frankfurt, Germany

"Woodkat" schrieb im Newsbeitrag
...
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.

"Peo Sjoblom" wrote:

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

"Woodkat" wrote:

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?

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" wrote:

On Wed, 8 Dec 2004 15:23:01 -0800, "Woodkat"
wrote:

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

On Thu, 9 Dec 2004 14:09:02 -0800, "Woodkat"
wrote:

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