Fill in missing values

[Dr. Demento]

When I receive data, all ID need to be unique strings, even if they're blank or all 'x' as a place holder. For these, I assign a pseudo ID (starting at one). I've even come up with a way to ensure that as long as I run the sub in a single session, the pseudoID don't get reused (I used a Public variable as a counter).

This works fine until I need to add more data at a later date. Then, the pseudoID counter gets reset to one and I have duplicate pseudoIDs.

My question: is there a way to evaluate the pseudoID strings and avoid any duplicates? The data is dynamic, so additions and deletions will occur. This means that the data may look like this, where "blank" is a new person added who does not have an ID but does have name and other associated data:

PseudoID
000000001 |Other data
000000002 |Other data
000000003 |Other data
blank |Other data
blank |Other data
000000006 |Other data
000000007 |Other data
blank |Other data
blank |Other data
000000010 |Other data

The four blanks could be converted to: 000000004, 000000005, 000000008, 000000009; alternatively, they could be converted to: 000000011, 000000012, 000000013, 000000014. Either solution is acceptable.

The downside is that most folks have a full ID, so converting each ID from a string to an integer, evaluating it, and re-converting it back to a string would be pretty time-consuming.

My current approach doesn't use arrays, but given how time-efficient they are, that's probably the next step.

I'm pretty sure this has been done by someone, somewhere; I just can't think of the appropriate search terms to find it (close, but no cigar). Thoughts/suggestions??

Thanks, y'all.

My current code:

    Public intPseudoID2 As Long

Sub FormatByHeader()

  ' ~~ Keeps track of pseudo ID so that each number is only used once (but only for a single session)
  If intPseudoID2 = 0 Then
    intPseudoID = 1
  Else
    intPseudoID = intPseudoID2
  End If


  With ActiveSheet
    .UsedRange  ' ~ Reset used range
    Set rngUsed = rng_ActualUsed()  ' ~~ determine actual used range in each sheet
    Set rngX = Range("A1:" & Cells(1, rngUsed.Columns.Count).address)  ' ~~ Set for range of header row | cells function gives the last cell in row 1
    
    For Each cell In rngX
      With cell
        Select Case True
        
        Case .value Like "ID"
          For Each cellx In Intersect(cell.EntireColumn, rngUsed).Offset(1, 0).Resize(rngUsed.Rows.Count - 1)  ' ~~ confines column of corresponding header
            cellx = WorksheetFunction.Substitute(cellx, "-", vbNullString)  ' remove dashes
            
            If cellx = vbNullString Or cellx.value = "xxxxxxxxx" Then
              
              cellx.value = intPseudoID  ' ~~ assign pseudo ID for any blank ID
              cellx.NumberFormat = "@" 
              cellx = Application.Text(cellx, "000000000")  ' format as text instead of number but retain leading zeros
              intPseudoID = intPseudoID + 1
            Else
              cellx.NumberFormat = "@"
              cellx = Application.Text(cellx, "000000000")
            End If
          Next cellx

intPseudoID2 = intPseudoID