Hi Gurus,
I would like to seek your help on getting my file to have an auto numbering system. Giving each row a Unique ID or Reference ID.
Hoping that a specific format will auto fill on Unique ID column if Project Name column contains any value/text.
I've tried searching on google but the one I saw would take 41 line codes to build.
This is the format I'm going for:
SMART-ISG-000001
Here are some samples.
Attachment 632476
Last edited by EXCELBENCH; 07-17-2019 at 01:20 AM.
To have these IDs permanently written into a sheet it's best to use VBA code. If one uses formula, then the number assigned to a particular project could change when another project is added.
Try such code in the sheet module (not in general one):
Seems it's signifficantly less than 41 lines long :-)
Please Login or Register to view this content.
Best Regards,
Kaper
Use this formula in A3
=left(a2,15)&value(mid(a2,11,6)+1)
This is amazing! Really. But do you think we can do this without using VBA or Macro?Originally Posted by Kaper
To have these IDs permanently written into a sheet it's best to use VBA code. If one uses formula, then the number assigned to a particular project could change when another project is added.
Try such code in the sheet module (not in general one):
Seems it's signifficantly less than 41 lines long :-)Please Login or Register to view this content.
did you check this EXCELBENCH ?
did you check this EXCELBENCH ?
Have YOU checked it, Chandy123987? If you have, then you will know that it doesn't work - it returns a #VALUE! error.
Excel 2016 (Windows) 32 bit
Unique ID Project Name Alpha Beta SMART-ISG-000001 Charlie SMART-ISG-000002 Douggie SMART-ISG-000003 SMART-ISG-000004 SMART-ISG-000005 SMART-ISG-000006
Sheet: Sheet1
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
i checked this it is working Aligw
Pls check in the attachment
No, it isn't. See my post above.
Your formula refers to A2, which is blank.
ok agreed that
In the attachment, you have added an entry in A2. You did not say that in your instructions.
It's the same as your own thread asking for help: NOT ENOUGH DETAIL!!!
Slow down and do things properly, please.
ok thanks for your advise
Sorry but this isn't what I'm looking for.
I'm actually going for an auto populate whenever a new value/text are entered on the Project column.
So mr bench
if you want a formula one that will work in A2 and copied down is =IF(B2<>"","SMART-ISG-"&TEXT(ROW()-1,"000000"),"")
it will show nothing until there is a value in the project name. just have to copy it far enough down, or make your table a table then each row added will get the formula populated
As mentioned by Kaper, if you use formulas for this sort of thing they are prone to cause variable results if =, for example, you add a row, the id numbers will remain with their rows rather than remaining with the project number. Also, in the use of your sheet, will a project name ever repeat? If it will this solution won't work.
Hope this helps
Ron
Yes sir. There would be same Project Names.
Last edited by AliGW; 07-17-2019 at 12:48 AM. Reason: Please don't quote unnecessarily!
Well in that case
put the first ID in manually in A2 then the following in a3 downwards
=IF(B3="","",IF(ISNUMBER(MATCH(B3,$B$2:B2,0)),INDEX($A$2:$A2,MATCH(B3,$B$2:B2,0)),LEFT(A2,10)&TEXT(1+RIGHT(A2,6),"000000")))
This will pick the ID used previously if the project number repeats.
This is the additional information needed to get a solution that will work for you, problem with an overly simplified sample...
Ron
Appreciate it Sir. I'll try this out.
Last edited by AliGW; 07-17-2019 at 12:47 AM. Reason: Please don't quote unnecessarily!
It gives me random numbers.
Supposed to be giving me unique numbers.
Please check the file..
Last edited by EXCELBENCH; 07-17-2019 at 01:20 AM.
The numbers are not random:
This will pick the ID used previously if the project number repeats.
The first formula he gave works. Thanks Sir rondeondon!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks