TEN OLDEST DATES

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 ..
 
Hi!
that have not been closed (column B)
Click to expand...

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
 
Clarification:
I've assumed that data starts in row2 down in sheet: Overall
(row1 would contain col headers)
 
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.
 
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.
Click to expand...

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)
Click to expand...

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
...
 
Max,

It is still giving me the Repair Clin info/date. By the formula, that
should not show.

Roy
 
roy.okinawa said:
.. It is still giving me the Repair Clin info/date.
By the formula, that should not show.
Click to expand...

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
 
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,""))
 
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.
 
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.
 
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.
Click to expand...

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(ISNUMBER(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
 
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.
Click to expand...

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
 
That did it.

Thank you very much.

Max said:
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(ISNUMBER(SEARCH("Repair
Clin",S2)))),
O2+ROW()/10^10,""))
Click to expand...
 
Back
Top