Bringing to a cell the last date an event occurred

  • Thread starter Thread starter XLFanatico
  • Start date Start date
X

XLFanatico

This is a long worksheet and need a formula, but don't know if possible.
A COLUMN B COLUMN C COLUMN
Doe, John Jan 05 a
Doe, Jane Jan 16 a
Presley, Elvis Jan 22
Doe, Jane Jan 30 a

All the previous data will be in SHEET 1
Now, in SHEET 2 :
COLUMN A = all possible names in COLUMN A SHEET 1,
written by me.
The formula will be used in COLUMN B, SHEET 2 and will tell me
which was the last date for example DOE,JANE had a letter "a"
(Jan 30 should be the result of the formula)
I'm thinking in VLOOKUP formula but don't know how to
organize it.
Thanks.
 
Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
Mike H said:
Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
Click to expand...
Thanks for your interest and response MIKE.
The provided formula gives me a number "0".
And I really need as an answer is a DATE.
A DATE where the last "a" was found on DOE, JANE.
Thanks
 
Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.
 
Fred Smith said:
Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.



.
Yes, I did enter as array.
Click to expand...
The result still a number instead of a date.
Does it make a difference if I'm using XL 2007 ?.
Sorry for the lack of information in my part.
Thanks.
 
First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.
 
Fred Smith said:
First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.



.
DO NOT PRESUME about zero, I said a number and the last time I
Click to expand...
check zero was a number.
I however need a date not a number or zero.
(cells are format as date already)
 
Back
Top