How to Genarate random letter
 

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.

How to Genarate random letter
 

Using the count, try this....

Letter = Chr(Count + 64)

"Ed Dror" wrote in message
...
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.

How to Genarate random letter
 

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!

Ed Dror wrote:

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.

--

Dave Peterson

How to Genarate random letter
 

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

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

Copy down as far as required.


In article ,
"Ed Dror" wrote:

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.

How to Genarate random letter
 

Thank you all very much, It working just fine


"Ed Dror" wrote in message
...
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.