In Excel, how to add serial number to only unique values in a column and let the duplicate values have same serial number as the original one?

I am trying to add a serial number to the rows in an excel sheet. I have multiple duplicate values in "ID" column and I am hiding the duplicate values first and then trying to add the serial number only for the unique values. I want all the duplicate values to have same serial number as the original value.

My sheet looks like this now :

It looks like this after hiding the duplicates:

What I am trying to achieve looks like this:

There are more than 50K rows and I am hoping that there is some way to do this in excel without manually filling in the numbers in the "No." column. Any help would be greatly appreciated!

This can be done with a simple formula:

=IF(B2=B3,A2,A2+1)

First put the value "1" in cell "A2". Then put the mentioned value below and continue dragging and dropping.

Your result will look as follows: