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

G

Guest

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
 
G

Guest

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
 
B

Bob Phillips

=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)
 
G

Guest

ufo_pilot said:
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
 
G

Guest

Bob Phillips said:
=MAX(IF(PosterRange="Margo",DateRange))

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

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

Thanks
 
G

Guest

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

Bob Phillips

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)
 
G

Guest

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

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.
 

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

Top