ExcelBanter - Bringing to a cell the last date an event occurred

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Bringing to a cell the last date an event occurred (https://www.excelbanter.com/excel-discussion-misc-queries/250906-bringing-cell-last-date-event-occurred.html)

Bringing to a cell the last date an event occurred

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.

Bringing to a cell the last date an event occurred

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

"XLFanatico" wrote:

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.

Bringing to a cell the last date an event occurred

"Mike H" wrote:

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

"XLFanatico" wrote:

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

Bringing to a cell the last date an event occurred

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.

"XLFanatico" wrote in message
...

"Mike H" wrote:

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

"XLFanatico" wrote:

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

Bringing to a cell the last date an event occurred

"Fred Smith" wrote:

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.

"XLFanatico" wrote in message
...

"Mike H" wrote:

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

"XLFanatico" wrote:

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

.
Yes, I did enter as array.
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.

Bringing to a cell the last date an event occurred

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.

"XLFanatico" wrote in message
...

"Fred Smith" wrote:

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.

"XLFanatico" wrote in message
...

"Mike H" wrote:

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

"XLFanatico" wrote:

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

.
Yes, I did enter as array.
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.

Bringing to a cell the last date an event occurred

"Fred Smith" wrote:

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.

"XLFanatico" wrote in message
...

"Fred Smith" wrote:

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.

"XLFanatico" wrote in message
...

"Mike H" wrote:

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

"XLFanatico" wrote:

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

.
Yes, I did enter as array.
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.

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