RankIF Assistance

[rkeen0822]

I am trying to a Rank IF Statement to rank all Like entries based off their date to be produced. Below is a sample of what I am trying to rank however I just can't seem to get it to work. Thanks in advance for all the assistance.

If status is "Clear to Pack" then rank "X1" Line using the date due, and use for each additional line.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]RANK[/TD]
[TD]ORDER[/TD]
[TD]LINE[/TD]
[TD]DATE DUE[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CLA126[/TD]
[TD]X1[/TD]
[TD]7/26/16[/TD]
[TD]CLEAR TO PACK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RAF154[/TD]
[TD]X2[/TD]
[TD]7/25/16[/TD]
[TD]NOT READY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RTS025[/TD]
[TD]X4[/TD]
[TD]8/1/16[/TD]
[TD]NOT READY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOP111[/TD]
[TD]X3[/TD]
[TD]7/20/16[/TD]
[TD]CLEAR TO PACK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]WET000[/TD]
[TD]X3[/TD]
[TD]7/30/16[/TD]
[TD]CLEAR TO PACK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ERI090[/TD]
[TD]X3[/TD]
[TD]8/1/16[/TD]
[TD]NOT READY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]KRY451[/TD]
[TD]X5[/TD]
[TD]7/27/16[/TD]
[TD]NOT READY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DAD191[/TD]
[TD]X5[/TD]
[TD]7/16/16[/TD]
[TD]NOT READY[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EAW165[/TD]
[TD]X7[/TD]
[TD]8/3/16[/TD]
[TD]CLEAR TO PACK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EAD111[/TD]
[TD]X6[/TD]
[TD]8/3/16[/TD]
[TD]CLEAR TO PACK[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]POP135[/TD]
[TD]X1[/TD]
[TD]7/27/16[/TD]
[TD]NOT READY[/TD]
[/TR]
</tbody>[/TABLE]

 

[peterhinton]

rank it how, what is top rank, and what is bottom rank etc. ?

 

[rkeen0822]

rank by date (earliest date would be 1) but for each of the lines that have a status of Clear to Pack

Thanks for help

 

[peterhinton]

=IF(E2="CLEAR TO PACK",MATCH(A2,LARGE(IF($D$2:$D$12=D2,$A$2:$A$12),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$12,D2)))),0),"")
Put this in A2, make it an array by Pressing CTRL+Shift+Enter, may need to make the dates format as general, to still see the date id add in an extra column, reason for this is we are not actually using the rank formula, its a countif, sort of.

 

[rkeen0822]

=IF(E2="CLEAR TO PACK",MATCH(A2,LARGE(IF($D$2:$D$12=D2,$A$2:$A$12),ROW(INDIRECT("1:"&COUNTIF($D$2:$D$12,D2)))),0),"")
Put this in A2, make it an array by Pressing CTRL+Shift+Enter, may need to make the dates format as general, to still see the date id add in an extra column, reason for this is we are not actually using the rank formula, its a countif, sort of.

I tried this Peter and it is still reflecting everything as a blank on all 5000 records? Above is just a few of them as I didn't want to overload the forum. Not too sure but I input the formula however not getting the result? Also, I don't see any reference to the "Line" column, would this still work with this?

 

[peterhinton]

how does the line column come into it ?
I may be missing something, I assumed you want to rank the date, based on the status ?

I used your example you had given and it worked for myself using UK dates,

 

[rkeen0822]

each line needs to have its own set of ranking, so if line = X1 and Status is clear to pack, then rank the date, same with X2-X7.