Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
I need a formula on my "Problem Areas" sheet that will search and then list
from my "Overall" sheet the ten oldest dates (column O), that have not been closed (column B), and do not match the text "Repair Clin" (column S). Each row of data begins with the purchase order# (column A). On the "Problem Areas" sheet it will only list the PO# and Date Opened. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
One play, which also caters for the possibility of ties in the dates ..
In sheet: Overall ----------- Use an empty col to the right, say, col T Put in T2: =IF(O2="","",IF(AND(B2<"Closed",S2<"Repair Clin"), O2+ROW()/10^10,"")) Copy T2 down to say, T100, to cover the max expected extent of data (Leave T1 empty) In sheet: Problem Areas ----------- With headers placed in A1:B1 : PO#, Date Opened Put in A2: =IF(ISERROR(SMALL(Overall!$T:$T,ROWS($A$1:A1))),"" , INDEX(Overall!A:A,MATCH( SMALL(Overall!$T:$T,ROWS($A$1:A1)),Overall!$T:$T,0 ))) Put in B2: =IF(ISERROR(SMALL(Overall!$T:$T,ROWS($A$1:A1))),"" , INDEX(Overall!O:O,MATCH( SMALL(Overall!$T:$T,ROWS($A$1:A1)),Overall!$T:$T,0 ))) Format B2 as date Select A2:B2, copy down to B100 (cover the same extent as done in col T in "Overall") Cols A & B will return the required data, with the lines sorted by dates in ascending order in col B and all neatly bunched at the top. POs with tied dates, if any, would be listed in the same relative order that they appear in "Overall". Just pick the data as required for the "10 oldest dates" appearing in col B (may need to pick more than 10 lines if there are tied dates) Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... I need a formula on my "Problem Areas" sheet that will search and then list from my "Overall" sheet the ten oldest dates (column O), that have not been closed (column B), and do not match the text "Repair Clin" (column S). Each row of data begins with the purchase order# (column A). On the "Problem Areas" sheet it will only list the PO# and Date Opened. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Hi!
that have not been closed (column B) Does that mean the text "Closed" may or may not be in column B? Try this: Array entered using the key combo of CTRL,SHIFT,ENTER: =SMALL(IF((Overall!B$2:B$20<"closed")*(Overall!S$ 2:S$20<"repair clin"),Overall!O$2:O$20),ROWS($1:1)) Copy down 10 rows. Biff "roy.okinawa" wrote in message ... I need a formula on my "Problem Areas" sheet that will search and then list from my "Overall" sheet the ten oldest dates (column O), that have not been closed (column B), and do not match the text "Repair Clin" (column S). Each row of data begins with the purchase order# (column A). On the "Problem Areas" sheet it will only list the PO# and Date Opened. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Clarification:
I've assumed that data starts in row2 down in sheet: Overall (row1 would contain col headers) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
I have done the first step on the Overall sheet and get dates on cells in
column S that do not have Repair Clin, they are blank. "Max" wrote: One play, which also caters for the possibility of ties in the dates .. In sheet: Overall ----------- Use an empty col to the right, say, col T Put in T2: =IF(O2="","",IF(AND(B2<"Closed",S2<"Repair Clin"), O2+ROW()/10^10,"")) Copy T2 down to say, T100, to cover the max expected extent of data (Leave T1 empty) In sheet: Problem Areas ----------- With headers placed in A1:B1 : PO#, Date Opened Put in A2: =IF(ISERROR(SMALL(Overall!$T:$T,ROWS($A$1:A1))),"" , INDEX(Overall!A:A,MATCH( SMALL(Overall!$T:$T,ROWS($A$1:A1)),Overall!$T:$T,0 ))) Put in B2: =IF(ISERROR(SMALL(Overall!$T:$T,ROWS($A$1:A1))),"" , INDEX(Overall!O:O,MATCH( SMALL(Overall!$T:$T,ROWS($A$1:A1)),Overall!$T:$T,0 ))) Format B2 as date Select A2:B2, copy down to B100 (cover the same extent as done in col T in "Overall") Cols A & B will return the required data, with the lines sorted by dates in ascending order in col B and all neatly bunched at the top. POs with tied dates, if any, would be listed in the same relative order that they appear in "Overall". Just pick the data as required for the "10 oldest dates" appearing in col B (may need to pick more than 10 lines if there are tied dates) Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... I need a formula on my "Problem Areas" sheet that will search and then list from my "Overall" sheet the ten oldest dates (column O), that have not been closed (column B), and do not match the text "Repair Clin" (column S). Each row of data begins with the purchase order# (column A). On the "Problem Areas" sheet it will only list the PO# and Date Opened. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Roy,
I missed out stating the assumption that data starts in row2 down in sheet: Overall earlier, sorry. Try this sample file with the implemented construct: http://cjoint.com/?lrfgbFeB5s roy_okinawa_10_OLDEST_DATES_wks.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... I have done the first step on the Overall sheet and get dates on cells in column S that do not have Repair Clin, they are blank. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
"roy.okinawa" wrote
I have done the first step on the Overall sheet and get dates on cells in column S that do not have Repair Clin, they are blank. Roy, Don't think there's anything wrong with your observation above (wrt what appears in col T), if I've interp'd your criteria correctly. Don't worry what appears in col T, look more at the results extracted in "Problem Areas" sheet. I've provided the sample earlier to illustrate. Are they correct ? Col T in "Overall" is a criteria col with an arbitrary tie-breaker built-in to differentiate any lines with duplicate dates in col O satisfying the criteria which might arise. The criteria used was framed based on your orig. post's specs: .. from my "Overall" sheet dates (column O), that have not been closed (column B), and do not match the text "Repair Clin" (column S) I made 2 key accompanying assumptions: (1) that the indication in col B to pick up was "Closed", and (2) that there might be POs with duplicate dates which satisfy the criteria you stated, hence requiring a tie-breaker. Did you get the correct results when you adapted the formulas to suit your actual sheet? Col T's criteria capture could be re-tweaked if the results returned are somehow not consistent with what you really want. Pl feedback ... -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Max,
It is still giving me the Repair Clin info/date. By the formula, that should not show. Roy "Max" wrote: Clarification: I've assumed that data starts in row2 down in sheet: Overall (row1 would contain col headers) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
"roy.okinawa" wrote:
.. It is still giving me the Repair Clin info/date. By the formula, that should not show. Yes, it shouldn't be picking up lines with "Repair Clin" in col S since we have this formula copied down in the criteria col T earlier: =IF(O2="","",IF(AND(B2<"Closed",S2<"Repair Clin"), O2+ROW()/10^10,"")) (The sample construct posted earlier in the other branch of the thread illustrates this) If you'd like to, email over a zipped copy of your file to me at: demechanik <at yahoo <dot com and I'll try to unravell what's happening over there .. (Need to wait awhile, though, as I can only access my yahoo account in about 10-11 hours time) Alternatively, you might consider uploading a "sanitized" small sample copy of your file via a free filehost* and then post the link to it in response here (the link is generated when you upload, just copy and paste it here) *Some free filehosts that could be used: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php For cjoint.com (it's in French), just click "Browse" button, navigate to folder select the file Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. But kindly note that no attachments should be posted *directly* to the newsgroup -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Roy,
Just a side thought .. Maybe there are "invisible" whitespaces within the data in col S preventing the correct pick up of the phrase "Repair Clin". We could try using TRIM in the criteria col T to make it more robust: In sheet: Overall Put instead in T2, and copy down: =IF(O2="","",IF(AND(TRIM(B2)<"Closed",TRIM(S2)<" Repair Clin"), O2+ROW()/10^10,"")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
As I looked through my earlier posts, I see that I didn't clarify that there
may be more text along with Repair Clin in Column S. Repair Clin will always come first followed by a period and then, if any, the remaining text. "Max" wrote: "roy.okinawa" wrote: .. It is still giving me the Repair Clin info/date. By the formula, that should not show. Yes, it shouldn't be picking up lines with "Repair Clin" in col S since we have this formula copied down in the criteria col T earlier: =IF(O2="","",IF(AND(B2<"Closed",S2<"Repair Clin"), O2+ROW()/10^10,"")) (The sample construct posted earlier in the other branch of the thread illustrates this) If you'd like to, email over a zipped copy of your file to me at: demechanik <at yahoo <dot com and I'll try to unravell what's happening over there .. (Need to wait awhile, though, as I can only access my yahoo account in about 10-11 hours time) Alternatively, you might consider uploading a "sanitized" small sample copy of your file via a free filehost* and then post the link to it in response here (the link is generated when you upload, just copy and paste it here) *Some free filehosts that could be used: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php For cjoint.com (it's in French), just click "Browse" button, navigate to folder select the file Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your response here. But kindly note that no attachments should be posted *directly* to the newsgroup -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
I typed just Repair Clin in the cell it works fine. It is the other text in
the cell that is causing a false response. In response to one of your earlier questions, the info extracted on the Problem Areas sheet does show only those docs that have not been closed. That is exactly what I wanted. However, the Repair Clin problem from the Overall sheet is transferring over. Thank you for your patience. "Max" wrote: Roy, Just a side thought .. Maybe there are "invisible" whitespaces within the data in col S preventing the correct pick up of the phrase "Repair Clin". We could try using TRIM in the criteria col T to make it more robust: In sheet: Overall Put instead in T2, and copy down: =IF(O2="","",IF(AND(TRIM(B2)<"Closed",TRIM(S2)<" Repair Clin"), O2+ROW()/10^10,"")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
"roy.okinawa" wrote
I typed just Repair Clin in the cell it works fine. It is the other text in the cell that is causing a false response. Aha, that's a crucial piece of info. This should do it: In sheet: Overall -------------------- Put instead in T2, and copy down: =IF(O2="","",IF(AND(TRIM(B2)<"Closed",NOT(ISNUMBE R(SEARCH("Repair Clin",S2)))), O2+ROW()/10^10,"")) The change for the checking on col S to: ... NOT(ISNUMBER(SEARCH("Repair Clin",S2))) ... should prove sufficient -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
"roy.okinawa" wrote
As I looked through my earlier posts, I see that I didn't clarify that there may be more text along with Repair Clin in Column S. Repair Clin will always come first followed by a period and then, if any, the remaining text. As posted in the other branch, changing this check on col S: ... S2<"Repair Clin" .. to ... NOT(ISNUMBER(SEARCH("Repair Clin",S2))) ... should now be enough as the refined criteria, viz.: In sheet: Overall -------------------- Put instead in T2, and copy down: =IF(O2="","",IF(AND(TRIM(B2)<"Closed",NOT(ISNUMBE R(SEARCH("Repair Clin",S2)))), O2+ROW()/10^10,"")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
That did it.
Thank you very much. "Max" wrote: "roy.okinawa" wrote As I looked through my earlier posts, I see that I didn't clarify that there may be more text along with Repair Clin in Column S. Repair Clin will always come first followed by a period and then, if any, the remaining text. As posted in the other branch, changing this check on col S: ... S2<"Repair Clin" .. to ... NOT(ISNUMBER(SEARCH("Repair Clin",S2))) ... should now be enough as the refined criteria, viz.: In sheet: Overall -------------------- Put instead in T2, and copy down: =IF(O2="","",IF(AND(TRIM(B2)<"Closed",NOT(ISNUMBE R(SEARCH("Repair Clin",S2)))), O2+ROW()/10^10,"")) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
TEN OLDEST DATES
Great to hear that !
You're welcome, and thanks for the feedback. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "roy.okinawa" wrote in message ... That did it. Thank you very much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |