Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
macro | Excel Discussion (Misc queries) | |||
date formula | Excel Discussion (Misc queries) | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
Find a time value in one column based on names in another | Excel Discussion (Misc queries) |