inputing a lotid and returning the lowest or top values in a list of lotids

see enclosed file as well. I tried to indicate in the file what i was looking for. I am not sure this is even possible without macros but i hope it is.

I have this raw data (see raw data worksheet).


I want to input a lotid and then have the top 5 (if there are 5) values returned based on the lotid that i enter. If the status1 is "Not yet due" then the # of days will be greater than zero indicating that something is planned in the future by this many days and i want the top 5 (ie...the 5 with the lowest numbers) ....ie the ones that will be occuring the soonest.

If the status1 is "Not met and not complete" then the # of days will be negative......indicating that something is overdue........in this case i again want the top 5 lowest ......ie the most overdue.

Again, i dont even know if this is possible.......just hoping that it is. A

Any help you can give to move me along the path of finding the answer would be appreciated.

Hi,
I did a pivot table with your data. Does it help answer your question? See attached.

See attached for another method:

in H29:

Please Login or Register  to view this content.

in I29:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied across L29

in M29:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

all formulas then copied down 5 rows.

Please Login or Register  to view this content.

The first part IF($M29="","" checks if there is a LotId in column M and returns a blank if not.


The second part of the formula is processed if lotid exists in column M:

INDEX(B$2:B$1421,SMALL(IF($A$2:$A$1421=$I$26,IF($F$2:$F$1421=$M29,ROW($F$2:$F$1421)-ROW($F$2)+1)),COUNTIF($M$29:$M29,$M29)))

is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF($A$2:$A$1421=$I$26,IF($F$2:$F$1421=$M29,ROW($F$2:$F$1421)-ROW($F$2)+1)

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if the lotid is found in range $A$2:$A$1421 and # of days in $F$2:$F$1421 matches the number of days extracted in column M with a similar formula to this, and then it will return the corresponding row number within the range. The ROW($F$2)+1 is added for robustness (incase you insert rows above, then the result won't skew) and to offset the row count to start with 1 at Row 2.

The last part COUNTIF($M$29:$M29,$M29)) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA. It counts increasing by one as you copy down.

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

Then you copy down.

Hope this clarifies a bit.
__________________

ROW($F$2:$F$1421)-ROW($F$2)+1 doesn't return TRUE/FALSEs it returns an array of row numbers from 1 to 1420

So first this part: IF($A$2:$A$1421=$I$26,IF($F$2:$F$1421=$M33 is processed.. this part IF($A$2:$A$1421=$I$26 returns TRUEs and FALSEs and then for all the TRUEs further test of IF($F$2:$F$1421=$M33 is done, so that the TRUEs line up for both conditions.. we could have also done IF(($A$2:$A$1421=$I$26)*($F$2:$F$1421=$M33),.... which returns 1s and 0s after multiplying arrays of TRUEs and FALSEs. The final sets of TRUEs (or 1's if you use latter method) lines up with the Row numbers from ROW($F$2:$F$1421)-ROW($F$2)+1. Then Smallest row number is taken (where it lines up with the first TRUE result) because the k factor for the SMALL function is 1 from the COUNTIF($M$29:$M29,$M29) and that is INDEXed against B$2:B$1421 to return the first date...

when you copy formula down then COUNTIF($M$29:$M30,$M30) returns 2 and so that means the second Smallest row number is returned (where TRUE result from the 2 conditions) and that is INDEXed against B$2:B$1421 to return the second date... and so on.

Note: If you reduce the range sizes to say rows 175 to 185 in the formula, and CSE confirm it, and copy it down, then go to Formulas | Evaluate Formula, you can see the steps it takes to give the results....

When you confirm a formula with CSE and it contains ROW($F$2:$F$1421)-ROW($F$2)+1 then it produces an array of numbers....

e.g

{2,3,4,5,6,7,8,9........1421}

the -ROW($F$2) part converts that array to:

{0,1,2,3,4,5,6,7,.......1419}

and the +1 re-aligns it as

{1,2,3,4,5,6,7,8,.......1420}

So the SMALL() takes the smallest if these numbers where the conditions checked yield a final result of TRUE... that is the position number needed in the INDEX(range,ver_position) function.

You could theoretically just use ROW($F$1:$F$1420) to give you {1,2,3,4,5,6,7,8... 1420}.. but our method adds a bit of robustness in case you add rows above, then it realigns correctly..

i swear to god...thought i was smart till i started reading your postings



that's frigin' awesome though...

Well it needs to be within a function to trigger that way...

e.g.

Try:

=SMALL(IF($A$175:$A$185=4050182,ROW($F$175:$F$185)-ROW($F$175)+1),1)

then go through the Evaluate Formula tool...

You should result in 6, meaning at the 6th row with range A175:A185 is the first TRUE result for $A$175:$A$185=4050182