Create random 6 digit number with no duplicates.

Hello,
Here is my scenario. I have an excel spreadsheet with Patient ID's. I am trying to create a 6 digit random number for each patient. Note that the script should automatically generate the random number in the adjacent cell each time a new patient is added to the spreadsheet. Once the random 6 digit number is generated, there are two scenarios to keep in mind.
1. The randomly generated numbers should not change. Once generated, each random number should remain static as it will be linked with the patient across time.
2. The random numbers should not repeat. Each randomly generated number must be unique to each patient.

There is only one master file and when the user enters a new patient into the spreadsheet, a random 6 digit number should automatically populate. (the random number should not repeat any previously generated numbers) The 6 digit number range can be set to 5000001 - 500999.

Thank you so much!

why not MAX(Current_number_to date) +1: start at 5000001 and just add sequentially for next patient.