Formula needed please

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I have two work sheets. The first contains the static client data while the
second records individual contacts with each client.

Looks like this;

Sheet 1
A B C D
# Ref# First Name
1 654 Sam Fisher
2 123 Sam Jones
3 688 Harry Smith
4 478 John Johanson

Sheet 2
A B C D E
Date # Ref# First Last
05/05/08 1 654 Sam Fisher
15/08/09 1 654 Sam Fisher
15/08/09 3 688 Harry Smith
15/08/09 4 478 John Johanson
15/08/09 5 987 Laurey Dessmond

I need a formula that can extract the most recent date we had contact with a
client based on the client #. In the case of Sam Fisher that would be the
number 1.

The formula would sit in column e on sheet 1

Is this possible? Please help

Thanks
 
Ben said:
how do i do that??
Click to expand...

Aha, I'm not sure why you can't see your earlier posting?

Here's the response I posted there:

... One way
Assuming client num is unique, and dates in Sheet2 are real dates
In Sheet1,
In E2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=MAX(IF(Sheet2!B$2:B$6=A2,Sheet2!A$2:A$6))
Copy down.

Real dates are numbers, increasing chronologically. Hence the latest date
for any particular client num would simply be its largest associated "date"
number
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
Thanks Mx...I couldn't see my first post because I got a time out message so
I didn't know it had been posted.

Thanks again...it works
 
Hi Max,

I now need to count how many clients are repeated in my record sheet. If
clients have a unique client number how do I work out how many clients
received a service on more than one occassion?
 
Back
Top