Macro/VBA to create sequential numbers A0001 to M9999

[elbonian]

We have a table of 120,000ish rows and the first column is a 'Serial Number" that has no real meaning and starts with A0001. The Serial number is increased with each entry to the table. The current high value is K8765.

We are proposing adding a few thousand records into this table and deleting hundreds to thousand of duplicates. I am proposing starting the Serial Number value over and re-sequencing the 120,000+ entries with new, sequential numbers, still starting at A0001.

Any great ideas on how to accomplish this so that the number are A0001-A9999, B0001-B9999, etc?

Thanks for all of your help.

 

[mrshl9898]

Does it need to be VBA?

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROUNDDOWN(ROW(A1)/1000,0)+1,1)&TEXT(ROW(A1)-(((ROUNDDOWN(ROW(A1)/1000,0)+1-1)*1000)),"0000")

 

[Peter_SSs]

Does it need to be VBA?

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROUNDDOWN(ROW(A1)/1000,0)+1,1)&TEXT(ROW(A1)-(((ROUNDDOWN(ROW(A1)/1000,0)+1-1)*1000)),"0000")

If the OP's description is accurate, then I can see a couple of issues with that ..
- You are changing letters after 999 instead of after 9999, which means it runs out of letters after 26,000 rows (simple enough to fix), and
- The formula returns include B0000, C0000 etc which the OP appears to want to skip.

so that the number are A0001-A9999, B0001-B9999, etc

Since a macro was requested, this is my attempt.

Sub Serial_Nums()
  Dim a As Variant
  Dim i As Long, k As Long

  k = 650000
  ReDim a(1 To Range("B" & Rows.Count).End(xlUp).Row - 1, 1 To 1)
  For i = 1 To UBound(a)
    k = k + 1 - (Right(k, 4) = "9999")
    a(i, 1) = Chr(Left(k, 2)) & Right(k, 4)
  Next i
  Range("A2").Resize(UBound(a)).Value = a
End Sub