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.

A	B	C	D	E
RANK	ORDER	LINE	DATE DUE	STATUS
	CLA126	X1	7/26/16	CLEAR TO PACK
	RAF154	X2	7/25/16	NOT READY
	RTS025	X4	8/1/16	NOT READY
	TOP111	X3	7/20/16	CLEAR TO PACK
	WET000	X3	7/30/16	CLEAR TO PACK
	ERI090	X3	8/1/16	NOT READY
	KRY451	X5	7/27/16	NOT READY
	DAD191	X5	7/16/16	NOT READY
	EAW165	X7	8/3/16	CLEAR TO PACK
	EAD111	X6	8/3/16	CLEAR TO PACK
	POP135	X1	7/27/16	NOT READY

<tbody>
</tbody>

 

[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.