How do you return the 5 most recent dates from a list?
Good afternoon!
I've got a tricky one that VLOOKUP, INDEX, and MATCH can't seem to handle. Well, perhaps they can..but I'm at a loss.
Imagine a list of dates with times attached; an Excel standard format. (10/26/06 1:30PM). For this task, they are due dates.
These are on worksheet 2. On worksheet 1, I'd like to display the 5 closest upcoming dates as well as two other items from the same row. As an additional caveat, I'd like only the dates from rows in which an adjacent cell does not have a "completed" date.
Essentially, worksheet 2 is set up as follows:
Project# | ProjectID | Employee | Description | TimeIn | TimeDue | TimeDone
So, the 5 closest upcoming values from column F that have no data in column G.
If, for some reason, there are fewer than 5 items, the sheet should just display how ever many there are.
This seems like a VLOOKUP problem, but with the time issue in column G, I've no good way how to handle it.
Thanks so very much!!
Dominic
Last edited by domcron; 10-27-2006 at 12:02 PM.
Well as long as the times are unique the following should work
if the date is in rows 2:16
=LARGE((ROW($F$2:$F$16)*NOT(($G$2:$G$16>0))),ROW(A1)) will identify the row
entered as an array shift ctrl held down then enter will return the rows the values occur in.
This can be copied down to the next 6 rows and will give you their row number
If the above in in cell J1
offset(f$1,j1-1,0) should return the maximum value, change the f for whatever columns you wish to return the data from and copy down for the other 6 values
Regards
Dav
Nearly there!
Hey Dav, thanks mate, this might be on the right track.
First, just FYI, I'm dealing with rows 6 through 35 for this.
I amended your formula to show that and did indeed get row numbers.Unfortunately, they are the last 5 rows in the range (31:35), despite the fact that they have no data in them.
One thing, out of curiosity, why is the last item "ROW(A1)" when there's no data there?
I tested your OFFSET idea with rows that do have data (overwriting your formula) and BAM! Your solution works.=LARGE((ROW($F$2:$F$16)*NOT(($G$2:$G$16>0))),ROW(A 1))
So, the only thing that needs to be sorted is how to get this formula to return the right row #'s!
Sooo close, I can taste it. This forum absolutely rocks.
Last edited by domcron; 10-27-2006 at 10:44 AM.
Well the row(a1) is the easy bit. Row(a1) returns the row a1 is on, unsurprisingly 1, however as there are no $ when this is copied down it becomes row(a2) which is 2!. This is the means to get the 5 highest numbers!
It depends how you adjusted my formula I was a bit asleep! Try ther following as the result
=LARGE((($F$6:$F$35)*NOT(($G$6:$G$35>0))),ROW(A1)) as an array this will give you the value, although you may need to format it as a date to look nice. If the above was in L20
=offset(f$5,match($l20,$G$6:$g$35,0),0)
I must have been asleep when it was the last post! It just returned the last 5 rows where the column g was blank. hope this works, if not get back to me.
Regards
Dav
Last edited by Dav; 10-30-2006 at 03:07 AM.
Now just to re-order the data...
Well Dav, your second version works much better! You are one slick cat.
We're certainly rockin now, but in the pursuit of a clean looking workbook, I was hoping to not have to have this info be on worksheet2, but have the formula sit on worksheet1. (Note: worksheet1 is called "Dashboard", worksheet2 is called "Projects").
So, using this formula below on "Dashboard"
=LARGE(((Projects!$F$6:$F$35)*NOT((Projects!$G$6:$G$35>0))),ROW(Projects!A1))
That works, but I do have two questions:
- Your formula returns zeros rather than blanks when their are less than 5 open projects. Any way to make it return blanks?
- Your formula takes the latest upcoming date and puts it first. In other words, the 5th most urgent project is at the top of the list while the most urgent is at the end. Wondering if there's some auto-sort for this or if it's more involved as I'm trying to have it all on that one sheet. I'm flexible on that though.
I'm happy to email the file to anyone who wishes to have a look. AIM is mercdominic, email is dominiccronshaw at gmail
One more round, Dav?Cheers mate!
Well the easy bit remember the row(a1), if you change it to 6-row(a1) it will return the 5 values the other way round! as rather than being 1,2,3,4,5 it will be 5,4,3,2,1 when you copy it down
to remove the 0
you need if(result=0,"",result)
this may also then need to be applied to the offset functions as well as they will return 0 as well
try
=if(LARGE(((Projects!$F$6:$F$35)*NOT((Projects!$G$6:$ G$35>0))),6-ROW(Projects!A1))=0,"",=LARGE(((Projects!$F$6:$F$35)*NOT((Projects!$G$6:$ G$35>0))),6-ROW(Projects!A1)))
nb in the forum it sometimes puts in nasty spaces that were not in the original post
Regards Dav
for the offset if($I20="","",offset(f$5,match($l20,$G$6:$g$35,0),0))
You will need to change the I20 to wahtever is appropriate
Regards
Dav
Ps if you are able to zip the file, it can be attached to your post in the forum
Posting file...
Thanks for that Dav. Zeroes are gone and we're looking grand.
Now, I'm nit-pickingbut the list now has blanks at the top and data for upcoming projects fills from the bottom first. I've attached the file for review.
Project_List_Detailed.zip
Basically, using this formula you gave me (note, I'm going for the top 7 projects rather than 5 so I'm using "8-row(Projects!a1)"):
I get a column with this (fyi, I have 2 active projects):=IF(LARGE(((Projects!$F$6:$F$35)*NOT((Projects!$G$6:$G$35>0))),8-ROW(Projects!A1))=0,"",LARGE(((Projects!$F$6:$F$35)*NOT((Projects!$G$6:$G$35>0))),8-ROW(Projects!A1)))
- Blank
- Blank
- Blank
- Blank
- Blank
- 11/2/2006
- 11/8/2006
Hoping to get this:
- 11/2/2006
- 11/8/2006
- Blank
- Blank
- Blank
- Blank
- Blank
So, yeah, nitpicking, for sure.
Well It becomes a little more complicated so I have done in on the attached sheet. Basically the row expression that you have got your head round now you have to adjust. You have already calculated the number of projects
min(8-row(a1),number of projects+1-row(a1)) Will give you the most recent in the top cell if there are blanks
As the calculation will go negative if there are < 7 projects, this would cause the large function to error as you can not have the 0 or -1 largest number, so to deal with this the expression which would evaluate as an error is put in an iserror function, to make this happy and set the cell to blanks
I have attached your sheet and just changed the large expressions to do all of this
Hopefully from the above you can make sense! Let me know if it works or makes sense
Regards
Dav
Wooha!! Success!
Well Dav,
You did it. That's exactly what I had envisioned when I started this last week.
Project_List_Detailed_10312006.zip
Thank you very much indeed mate.
I've attached the current version for anyone who might be interested.
Briefly: This is a basic project management workbook that allows you to input and track your tasks. The detail is very minimal to this point, but if anyone wants to build it out more, feel free.
On the Projects sheet, Requester is pulled using a named range from a 3rd worksheet (it must be changed if you wish to add any other names).
Thanks to Dav, the worksheet automatically pulls the 7 most upcoming projects and along with projectID and requester name. If the due date has passed, conditional formatting changes the date to red. All that's on the Dashboard worksheet.
A list of open and completed projects by requester also uses conditional formatting to highlight the qty of open projects by requester.
Graphical representations of open and completed projects round out this version.
Thanks again to all who responded on this one.
Cheers,
Dom
Last edited by domcron; 10-31-2006 at 06:30 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks