Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Letter Generator | Excel Discussion (Misc queries) | |||
random letter generation | Excel Worksheet Functions | |||
Genarate a fixed date with an IF function? | Excel Discussion (Misc queries) | |||
Return a random letter | Excel Programming | |||
Return a random letter | Excel Programming |