Formula needed please

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
 
M

Max

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
---
 
B

Ben

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
 
B

Ben

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with a formula 2
vlookup or Match 6
Pls help for Simple Calculation 1
Need to pull <=14 Days, <=30 Days, >30 Days from a date column 3
dates inbetween 3
Summing like data 5
caluculate an average based on 2 criteria 3
Date Formula 1

Top