#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
roy.okinawa
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating number of days between two dates that fall between two other dates [email protected] Excel Discussion (Misc queries) 5 October 26th 05 06:18 PM
Automatic shading of cells based on dates??? Pedros Excel Worksheet Functions 3 October 20th 05 12:35 AM
2 digit year in dates return 19xx not 20xx moranbo Excel Discussion (Misc queries) 1 September 7th 05 01:44 AM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM
due dates Niki New Users to Excel 4 January 10th 05 05:11 PM


All times are GMT +1. The time now is 10:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"