Change / Mask Client Sensitive Data

I have a long list of data (around 10,000 rows) with one column Client Sensitive (Column A see below) that needs to be changed before using in a pivot so the client won't see this data.

At the moment I'm using a formula but happy to use VBA for this as the rest of the report updates with VBA.

The formula that works but takes far too long is
=IF(COUNTIF($A$1:$A2,$A2)>1,VLOOKUP($A2,$A:$R,18,FALSE),MAX($R$1:$R1)+1)
Where column A is the sensitve data (and the unique identifier) and column R is the column the formula is entered.

The below is what I want, the column R I now use in a pivot to get the results I want then I can hide that column and delete Column A so no Client Sensitve data is with the spreadsheet.

However as I've said it takes far too long to calculate down 10,000 rows..

Column A
Column R

A12345
1

[Code] .....