• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

EXCEL Unique Unchanging ID using VBA

Pneuma66

New Member
Hi,
I have a worksheet that requires a unique ID in column B for each row that doesn't ever change once it's applied. I would like to concatenate the contents of column A along with a unique identifier value (It can be sequential numeric and doesn't need to be more than 4 characters long) Example: CSW-0415 where column A = CSW. Since rows will be both added and deleted from the worksheet, it has to never change the values once they are assigned. Can this be done using a macro or vba using a button?
 
Pneuma66
Your can ... answer is ... many things are possible,
especially after You've upload an Excel sample file,
which shows You data as close as possible with real data
as well as there are Your expected results based Your sample data.
 
My actual workbook has confidential data in it so I can't upload it. However, the attached workbook is sufficient to show what I'm trying to do. Thanks for any help.
 

Attachments

  • Sample File.xlsx
    11.6 KB · Views: 14
Pneuma66
You have asked to get an unique ID which is ... 8 long - okay?
How possible that Your sample unique ID is ... 10 long?
Where so You need any macro and when do it should work?
Is Your confidential file protected?
... or how do You prevent that those unique IDs nobody won't modify? ... including that A-column value.
Is there already any code?
What to do if there are not any more free sequential numeric parts?
You could check that manually with =COUNTIF(Table1[Unique ID],uniqueID) where uniqueID is Your new unique ID.
 
If a unique ID is assigned, and later that row is deleted, can that same unique ID be used again? If not, you're going to have to store all used IDs even after the row has been deleted.
 
Hi p45cal,
No. A uniqueID that is used once and deleted can't be used again because it would tie to a previous version. We do comparisons with prior versions sometimes to see what has changed between the two versions and if a uniqueID was reissued, it would result in an incorrect comparison. it has to be used once and never again. Thanks for the question.
 
Hi vietm,
the uniqueID should aways follow the format XXX-####. There is no need for it to ever vary. We won't have more than 9999 unique rows in the spreadsheet. The VBA should be tied to adding a completely new row of data and not to updates to that data. The file is not protected but the uniqueID column can be protected. Thanks.
 
Pneuma66
You seems to skipped few questions like:
Your the uniqueID should aways follow the format XXX-####.
Screenshot 2021-04-01 at 10.02.56.png ... except Your sample file which has format XXX - #####?
What matters about number of rows?
Aren't You interesting uniqueIDs?
Is there already any code?
If something would protect normal way then the file will be protect too.
... and that would effect to Your confidential data too.
 
Pneuma66. I think you need to have an Excel equivalent of the autoincrement columns available in most databases. Excel doesn't have that as far as I am aware. However I have written a data entry addin for Excel which does that among other things and I think will do what you are looking for. You can download it at dexelform.com. I attach your spreadsheet set up for use with Dexel Form it you want to give it a try.
 

Attachments

  • Sample File for DexelForm.xlsx
    20 KB · Views: 18
Back
Top