How to find the most recent date in a column based on other column
 

I am working on creating a worksheet which will help me keep track of the
players stats in a game I am running. The Spreadsheet currently Tracks the
following fields.

Date Posted, Poster, Contract/Objective, and Words
I enter the data daily as players submit their posts and record the date,
name, and count of their words.

Thus far I have figured out how to use named ranges to make a report on the
sheet to show total posts and wordcount.

Postcount: =COUNTIF(PosterRange,"=<postername")
Wordcount: =SUMIF(PosterRange,"=<postername",WordsRange)

The problem I have been encountering is that I have been having to manually
paste in the new last date posted into their row on the sheet.

What I would like to do is have that become a field which looks through the
PosterRange and finds the Last instance value that belongs to that Poster.
And then returns the DateColumn in that row.

I've tried a few different scenarios, but all of them seem to either return
a bad date (something ala 1900s), or returns the first date found for the
user and not the lance. I'm stumped here. Any suggestions?

Some sample data:

Date Poster Contract Words
Monday, October 02, 2006 John Objective-I 1705
Monday, October 02, 2006 Margo Objective-I 769
Tuesday, October 03, 2006 Kyle Objective-I 671
Thursday, October 05, 2006 Kyle Objective-I 1372
Thursday, October 05, 2006 Margo Objective-I 667

How to find the most recent date in a column based on other column
 

Have you tried applying an autofilter?
This should work
You may need more than one column filtered if you have the dates spread out
to more than one column.
you may have to first filter column "C" for the year
then pick up column "B" for the month etc


"Veretax" wrote:

I am working on creating a worksheet which will help me keep track of the
players stats in a game I am running. The Spreadsheet currently Tracks the
following fields.

Date Posted, Poster, Contract/Objective, and Words
I enter the data daily as players submit their posts and record the date,
name, and count of their words.

Thus far I have figured out how to use named ranges to make a report on the
sheet to show total posts and wordcount.

Postcount: =COUNTIF(PosterRange,"=<postername")
Wordcount: =SUMIF(PosterRange,"=<postername",WordsRange)

The problem I have been encountering is that I have been having to manually
paste in the new last date posted into their row on the sheet.

What I would like to do is have that become a field which looks through the
PosterRange and finds the Last instance value that belongs to that Poster.
And then returns the DateColumn in that row.

I've tried a few different scenarios, but all of them seem to either return
a bad date (something ala 1900s), or returns the first date found for the
user and not the lance. I'm stumped here. Any suggestions?

Some sample data:

Date Poster Contract Words
Monday, October 02, 2006 John Objective-I 1705
Monday, October 02, 2006 Margo Objective-I 769
Tuesday, October 03, 2006 Kyle Objective-I 671
Thursday, October 05, 2006 Kyle Objective-I 1372
Thursday, October 05, 2006 Margo Objective-I 667

How to find the most recent date in a column based on other column
 

=MAX(IF(PosterRange="Margo",DateRange))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Veretax" wrote in message
...
I am working on creating a worksheet which will help me keep track of the
players stats in a game I am running. The Spreadsheet currently Tracks
the
following fields.

Date Posted, Poster, Contract/Objective, and Words
I enter the data daily as players submit their posts and record the date,
name, and count of their words.

Thus far I have figured out how to use named ranges to make a report on
the
sheet to show total posts and wordcount.

Postcount: =COUNTIF(PosterRange,"=<postername")
Wordcount: =SUMIF(PosterRange,"=<postername",WordsRange)

The problem I have been encountering is that I have been having to
manually
paste in the new last date posted into their row on the sheet.

What I would like to do is have that become a field which looks through
the
PosterRange and finds the Last instance value that belongs to that Poster.
And then returns the DateColumn in that row.

I've tried a few different scenarios, but all of them seem to either
return
a bad date (something ala 1900s), or returns the first date found for the
user and not the lance. I'm stumped here. Any suggestions?

Some sample data:

Date Poster Contract Words
Monday, October 02, 2006 John Objective-I 1705
Monday, October 02, 2006 Margo Objective-I 769
Tuesday, October 03, 2006 Kyle Objective-I 671
Thursday, October 05, 2006 Kyle Objective-I 1372
Thursday, October 05, 2006 Margo Objective-I 667

How to find the most recent date in a column based on other co
 

"ufo_pilot" wrote:

Have you tried applying an autofilter?
This should work
You may need more than one column filtered if you have the dates spread out
to more than one column.
you may have to first filter column "C" for the year
then pick up column "B" for the month etc



For Simplicity's sake, Column A is the date actually stored as 10/06/2006
the long date format is how its displayed.

Column B is the Posters Name

Column C is the Contract/Objective

Column D is the Words

How to find the most recent date in a column based on other co
 

"Bob Phillips" wrote:

=MAX(IF(PosterRange="Margo",DateRange))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Hrm that works.... Finally figured out it wasnt the forumla the one range
was misdefined. that would mess it up I suppose.

Thanks

How to find the most recent date in a column based on other co
 

That works swell in fact I made an adaptation on it to take into account if
there is no date logged

=IF(MAX(IF(PosterRange="Margo",DateRange))=0,
"Never",MAX(IF(PosterRange="Margo",DateRange)) )

it has to be entered as an array forumla as before I believe, but with a
little conditional formatting I have those who never post with Never in red
Thanks for the help, Couldn't figure out why it wasn't working properly.

How to find the most recent date in a column based on other co
 

Why don't you just use the original formula, and C F on a value of 0 with a
red font and red background?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Veretax" wrote in message
...
That works swell in fact I made an adaptation on it to take into account
if
there is no date logged

=IF(MAX(IF(PosterRange="Margo",DateRange))=0,
"Never",MAX(IF(PosterRange="Margo",DateRange)) )

it has to be entered as an array forumla as before I believe, but with a
little conditional formatting I have those who never post with Never in
red
Thanks for the help, Couldn't figure out why it wasn't working properly.

How to find the most recent date in a column based on other co
 

"Bob Phillips" wrote:

Why don't you just use the original formula, and C F on a value of 0 with a
red font and red background?

--
HTH

Bob Phillips


yeah you could in theory do that, but I like it saying Never instead of 0...
or even blank could be used in this case. I decided to use Never.