grab max date from list
 

I have several columns of data. The most important being a client name and
their last date of service. Can I pivot and pull the patient name and only
grab their last date of service, or is there a vlookup that will grab the
most recent date?

Hi!

Why pull and grab when you can yank! <g

Client names in column A
Dates in column G

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100="Client_Name",G1:G100))

Format the cell as DATE

Biff

-----Original Message-----
I have several columns of data. The most important being
a client name and
their last date of service. Can I pivot and pull the
patient name and only
grab their last date of service, or is there a vlookup
that will grab the
most recent date?
.

=SUMPRODUCT(MAX((RngNames=E1)*(RngDates)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"jenn" wrote in message
...
I have several columns of data. The most important being a client name and
their last date of service. Can I pivot and pull the patient name and only
grab their last date of service, or is there a vlookup that will grab the
most recent date?

I must not have something quite right... when I follow the instructions my
column fills with the formula... =MAX(IF(A1:A100="Client_Name",G1:G100))

"Biff" wrote:

Hi!

Why pull and grab when you can yank! <g

Client names in column A
Dates in column G

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100="Client_Name",G1:G100))

Format the cell as DATE

Biff

-----Original Message-----
I have several columns of data. The most important being
a client name and
their last date of service. Can I pivot and pull the
patient name and only
grab their last date of service, or is there a vlookup
that will grab the
most recent date?
.

Sounds like your cells were probably formatted as Text *before* you entered
the formula.

To check if this is so, select one of the "bad" cells displaying the
formula, and then:

<Ctrl <Shift. <~
Then <F2
Then <Enter

What this does is use a keyboard shortcut to format the cell to General,
then re-enter the formula.

If this *doesn't* work, another possibility is that you're in "formula view
mode".

To toggle in and out of this view, hold <Ctrl, and then hit the key that
has the <~ on it.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jenn" wrote in message
...
I must not have something quite right... when I follow the instructions my
column fills with the formula... =MAX(IF(A1:A100="Client_Name",G1:G100))

"Biff" wrote:

Hi!

Why pull and grab when you can yank! <g

Client names in column A
Dates in column G

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A1:A100="Client_Name",G1:G100))

Format the cell as DATE

Biff

-----Original Message-----
I have several columns of data. The most important being
a client name and
their last date of service. Can I pivot and pull the
patient name and only
grab their last date of service, or is there a vlookup
that will grab the
most recent date?
.