VBA to replicate Excel ranking formula which uses sumproduct and crashing Excel

Hi, I have a formula which ranks values using a sumproduct. For example

=SUMPRODUCT(($A$2:$A$5815=A2)*(FY2>$FY$2:$FY$5815))+1

Problem is that it is very computational and its crashing my excel. Any chance somebody could assist with a VBA function or macro?

I have attached an Excel file with the output. Column D is what I'm looking to "fix"

Thanks a million!

Hope you can adjust the code.

Please Login or Register  to view this content.

Thanks for the feedback, the code works on a small data set but I have 150 000 rows...any thoughts on this?

How is it not working?

Any error?

No error just takes a very long time. Also the VBA isnt dynamic so had to move columns around to get it working. Thanks though

Try change to

Please Login or Register  to view this content.