VBA Replace duplicate number for Random Number

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 6, 2014
Messages
319
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

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
This should keep all original non-duplicate values and keep the first duplicated value while changing all the subsequent duplicates.
VBA Code:
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

Active Member
Joined
Nov 6, 2014
Messages
319
This should keep all original non-duplicate values and keep the first duplicated value while changing all the subsequent duplicates.
VBA Code:
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.
 

Forum statistics

Threads
1,141,203
Messages
5,704,926
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top