vba help needed: to create unique ID for each row in Excel Worksheet

[Elena Margulis]

Hi,
I have data entry Worksheet (Survey), plz see below, where I need to generate unique identifier for each record (each Survey):

I have hidden column - SurveyCode, where the ID could be stored.
I was thinking about different ways of doing this:
If create formula like Row() - populating new number on each record, then in case if user would delete a row, the SurveyCode would change for the next / previous records.
Same problem if I'd use DEC2HEX(RANDBETWEEN(0, 4294967295), 8), even worse, because it changes any time on any action(s)...

I will have unknown numbers of records in this form (could be many thousands), that's why hard-coding limited amounts of records won't work either, and also creates problems when data entry occurs.
Formulas placed into SurveyCode could be destroyed in case if user will accidentally press tab button (which will create an empty row in this Form) and so force...

So, I am really looking for vba code that would create a unique id - for the hidden SurveyCode field - for each record, in this data entry Worksheet.
After deleting/ adding record(s) - the ID for all (already entered) records must not be changed or deleted!

Ideally, my SurveyCode should have the following format FileName-yyyy-mm-uniqueID (for example CSS-2020-08-345677, where CSS is FileName (or any text), 2020-08 is current yr/mo, 345677 is unique id )

Please help!
Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

I need to generate unique identifier for each record (each Survey):

We don't know what a 'Survey' is in your sheet and there is no column shown with that title so it's a bit of a guess at the moment.

Do you mean a unique identifier for each row in the table?
If not, what do you mean?


What is the name of your table (found in name manager on Formulas ribbon tab)

Are there any other separate tables on this worksheet?

 

[Elena Margulis]

to Peter_SSs:
Yes, I mean - a unique identifier for each row in the table - below the headers (starting from A4)

'Survey' is just the header's name (no need a unique id on my headers; unique id needed for the actual rows - below Program / SurveyDate etc.)

The name of my table (I just checked Formulas / Name Mgr) is Table1
The name of the sheet where this Worksheet located is "ClientSatisfactionForm" (giving this info just in case)

 

[Peter_SSs]

Thanks. Give this a try with a copy of your workbook to see if it does what you want.

Sub CreateIDs()
  Dim IDPrefix As String
  
  IDPrefix = ThisWorkbook.Name
  IDPrefix = Left(IDPrefix, InStrRev(IDPrefix, ".") - 1) & Format(Date, "-yyyy-mm-")
  With Sheets("ClientSatisfactionForm").Range("Table1[SurveyCode]")
    .Cells(1).Formula = "=""" & IDPrefix & """&TEXT(ROW()-ROW(Table1[#Headers]),""000000"")"
    .Value = .Value
  End With
End Sub

 

[Elena Margulis]

to Peter_SSs
Thank you, I just tried - no luck...
But (before anything) my questions:
- Where should I paste this code - under the Sheet or under ThisWorkbook? On any specific event?
I tried posting into General (right-click, View Code) - nothing happened, after I created new records... SurveyCode is empty...
- What should now be in the SurveyCode field? Should I leave it empty in order your code to be working? Should I have Row() function filled inside SurveyCode?

This is the screenshot of where I pasted you code:

 

[Elena Margulis]

To Peter_SSs

May be I provided not the full info -
- My data entry Worksheet is macro enabled (.xlsxm) (if that's important)
- I store SurveyCode in the column AC. It's now empty (no formulas)

 

[Peter_SSs]

Perhaps I did not fully understand your requirement.

Let's try a different approach. You could remove the previous code. This is a Worksheet_Change event procedure so should run automatically any time something in the 'ClientSatisfactionForm' sheet is changed. To implement ..
1. Right click the 'ClientSatisfactionForm' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

I have assumed that column AC is part of 'Table1'

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim IDPrefix As String, s As String
  Dim IDSuffix As Long, IDMaxSuffix As Long, i As Long
  Dim a As Variant
  
  If Not Intersect(Target, Range("Table1")) Is Nothing Then
    IDPrefix = ThisWorkbook.Name
    IDPrefix = Left(IDPrefix, InStrRev(IDPrefix, ".") - 1) & Format(Date, "-yyyy-mm-")
    With Range("Table1[SurveyCode]")
      a = .Value
      For i = 1 To UBound(a)
        s = a(i, 1)
        If s Like "*-*-*-*" Then
          If Split(s, "-")(3) > IDMaxSuffix Then IDMaxSuffix = Split(s, "-")(3)
        End If
      Next i
      For i = 1 To UBound(a)
        If Len(a(i, 1)) = 0 Then
          IDMaxSuffix = IDMaxSuffix + 1
          a(i, 1) = IDPrefix & Format(IDMaxSuffix, "000000")
        End If
      Next i
      Application.EnableEvents = False
      .Value = a
      Application.EnableEvents = True
    End With
  End If
End Sub

 

[Elena Margulis]

Hi,
Thank you for all your efforts...

I tested, but doesn't seem to work correctly.
This time - it generates codes (as I requested), but after I am adding the next new record in the Worksheet (by pressing tab button) - it generates the number that is already exists in a previous record
(1st time I add new record - it generates unique number, but then - the following records create the same number <FileName+mm-yy+00009>)

Plz, see the screenshot (highlighted are the records that I added (starting from 000115), after pasting your code):

 

[Peter_SSs]

Problem is I hadn't allowed for the fact that there might be "-" character(s) in the workbook name.

Replace the previous code with this version.

Then delete those duplicated values in column AC. The code should immediately replace them with new, unique ones.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim IDPrefix As String, s As String
  Dim IDSuffix As Long, IDMaxSuffix As Long, i As Long
  Dim a As Variant, Bits As Variant
  
  If Not Intersect(Target, Range("Table1")) Is Nothing Then
    IDPrefix = ThisWorkbook.Name
    IDPrefix = Left(IDPrefix, InStrRev(IDPrefix, ".") - 1) & Format(Date, "-yyyy-mm-")
    With Range("Table1[SurveyCode]")
      a = .Value
      For i = 1 To UBound(a)
        s = a(i, 1)
        If s Like "*-*-*-*" Then
          Bits = Split(s, "-")
          If Bits(UBound(Bits)) > IDMaxSuffix Then IDMaxSuffix = Bits(UBound(Bits))
        End If
      Next i
      For i = 1 To UBound(a)
        If Len(a(i, 1)) = 0 Then
          IDMaxSuffix = IDMaxSuffix + 1
          a(i, 1) = IDPrefix & Format(IDMaxSuffix, "000000")
        End If
      Next i
      Application.EnableEvents = False
      .Value = a
      Application.EnableEvents = True
    End With
  End If
End Sub

 

[Elena Margulis]

To Peter_SSs

Fantastic !!!
Thank you so much

All works - I re-tested - added new records, deleted records, sorted etc
The SurveyCode doesn't change; stays at the appropriate record (unlike - in case of my other efforts using formulas)

If possible - just an additional question:
- If for example I would wanted to use any text instead of the FileName in a SurveyCode?
where should I update your code?

                  IDPrefix = Left(IDPrefix, InStrRev(IDPrefix, ".") - 1) & Format(Date, "-yyyy-mm-")

In this line - I think it separates the fileName from extension and adds a year...
What if I'd wanted just any text instead the FileName?