How to Genarate random letter

[Ed Dror]

Hi there,

I have a file look like this

VendorID (A1) and FileName (B1)
Look like this

9287 C:\myfilename.txt
9287 C:\myfilename.xls
9287 C:\myfilenamepdf
9287 C:\myfilename2.txt
9287 C:\myfilename.jpg
9283 C:\myfilename.jpg
9283 C:\myfilename.jpg

As you can see some vendors has 5 files and some 2 or 3 or 8 (15 files max)
All of the VendorID has 4 digit only

Because VendorID is duplicate I need to create a unique ID
So I'm thinking to add a letter at the end of the vendorId
Looks like this (new C1)

9287A
9287B
9287C
9287D
9287E
9283A
9283B

How do I add a with VBA function that assign the exact
letter based on count VendorID

Thanks,
Ed Dror
Andrew Lauren Co.

 

[dman]

Using the count, try this....

Letter = Chr(Count + 64)

 

[Dave Peterson]

Does it have to be VBA?

I'd insert a helper column and use this formula:

=A1&CHAR(64+COUNTIF($A$1:A1,A1))

In fact, if I were using code, I'd do it the same way:

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
.Columns(2).Insert
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

With myRng.Offset(0, 1)
.NumberFormat = "General"
.Formula = "=A1&CHAR(64+COUNTIF($A$1:A1,A1))"
.Value = .Value
End With

.Columns(1).Delete

End With

End Sub

Test it against a copy of your data--just in case!

 

[JE McGimpsey]

One way (assuming no more than 26 entries per VendorID):

=A1 & CHAR(64+ COUNTIF($A$1:A1,A1))

Copy down as far as required.

 

[Ed Dror]

Thank you all very much, It working just fine