VBA Replace duplicate number for Random Number

[Declamatory]

Hi Folks,

The company I work receives a daily file with a list of share trades. I use VBA to convert it to a file I can import to our operating system.

Each trade has a unique deal reference number (Column M) apart from when there is a transfer of shares between two clients. In that scenario both clients has a row in the spreadsheet for their side of the transfer but both rows have the same deal reference number.

The system we import to isn't fussy about what the reference number is but doesn't like duplicate reference numbers. I can manually change the duplicate number using the RAND function but I want to make the process for other users as easy as possible.

Is there a way as part of my VBA code I can replace any duplicate reference numbers in column M with a random number?

Thank you.

Declamatory

 

[shknbk2]

Do you have to keep at least one version of the duplicated number while you change all other duplicates, or can all reference numbers simply be generated as new to replace the existing numbers whether they are duplicated or not? I'm wondering if you can just replace all numbers with new numbers without needing to check whether existing ones are duplicated. It might be faster.

How many rows of data are we talking about?

What are examples of the numbers in column M (what is their format)?

 

[Declamatory]

I would ideally like to keep the numbers but no it isn't essential. All the numbers could be changed. The format is a 7 digit number but when I change manually using RAND() it changes to 0.3895241 and still work when importing.

 

[shknbk2]

This should keep all original non-duplicate values and keep the first duplicated value while changing all the subsequent duplicates.

Sub UniqueValues()
    Dim a() As Variant
    Dim unique As Boolean
    Dim i As Long, j As Long
    
    Randomize
    unique = False
    a = Range(Range("M1"), Cells(Rows.Count, 13).End(xlUp)).Value
    Do While Not unique
        unique = True
        For i = 1 To UBound(a, 1) - 1
            For j = i + 1 To UBound(a, 1)
                If a(i, 1) = a(j, 1) Then
                    unique = False
                    a(j, 1) = Int((9000000 * Rnd) + 1000000)
                End If
            Next j
        Next i
    Loop
    Range(Range("M1"), Cells(Rows.Count, 13).End(xlUp)).Value = a
End Sub

 

[Declamatory]

This should keep all original non-duplicate values and keep the first duplicated value while changing all the subsequent duplicates.

Sub UniqueValues()
    Dim a() As Variant
    Dim unique As Boolean
    Dim i As Long, j As Long
   
    Randomize
    unique = False
    a = Range(Range("M1"), Cells(Rows.Count, 13).End(xlUp)).Value
    Do While Not unique
        unique = True
        For i = 1 To UBound(a, 1) - 1
            For j = i + 1 To UBound(a, 1)
                If a(i, 1) = a(j, 1) Then
                    unique = False
                    a(j, 1) = Int((9000000 * Rnd) + 1000000)
                End If
            Next j
        Next i
    Loop
    Range(Range("M1"), Cells(Rows.Count, 13).End(xlUp)).Value = a
End Sub

That is really good! Thank you so much. I really appreciate it.

 

[shknbk2]

You're welcome