Multiple Criteria Ranking without Skipping Numbers when there are Duplicates

[LauraSophie]

Hi,
I am trying to rank a list of orders withing a container based on their shipment date. The difficulty is that within each container there can be multiople orders with the same shipment date. I can use SUMPRODUCT to rank multiple criteria and get the order within each container. The difficulty I am having is that when there are multiple orders with the same date within one container, the SUMPRODUCT formula skips numbers. Please can anyone help?
Below is an example of the data with what I would like the rank to return and what SUMPRODUCT is currently giving me. It would usually be thousands of lines that are added so sorting etc isn't an option and it needs to be formula driven though helper columns shouldn't be a problem if needed.
Any ideas would be greatly appreciated.
Thanks

[TABLE="width: 507"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CONTAINER[/TD]
[TD]ORDER SHIPDATE[/TD]
[TD]Unique Shipment
[/TD]
[TD]SUMPRODUCT[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]03/01/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]03/01/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]03/03/1901[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]03/03/1901[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]03/01/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]02/01/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]02/06/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]02/07/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]03/01/1900[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]03/03/1901[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bsquad]

I am not sure I understand what you mean by skipping them, if your purpose is to RANK; the value of 2 is technically correct. Are you trying to get a distinct/unique list for each container?

 

[LauraSophie]

I am not sure I understand what you mean by skipping them, if your purpose is to RANK; the value of 2 is technically correct. Are you trying to get a distinct/unique list for each container?

Hi, thanks.
Sorry the values in blue under 'Unique Shipment' are correct and what I would like the formulae to return but I have entered these manually. The column headed SUMPRODUCT is the one which is formula driven. The 2's are correct but then using the formula it skips to 4 when the next one should be listed as 3. Hope that makes sense. Thanks

 

[bsquad]

Assuming that your blue column is Col C:
Paste this in there, this isn't condition based; meaning for each for each Container, youll have to paste; condition based you are looking at an array most likely

=RANK(B2,$B$2:$B$5,1)+COUNTIF($B$2:B2,B2)-1

 

[LauraSophie]

Thanks - Sorry for the delay. I still haven't managed to crack this one!

Unfortunately it needs to be condition based as there are thousands of containers so I don't want to have to redo the formula for each one!