Good day all,
I need to generate unique ID references in a Non Conformance register & require this ref. to be relative to a particular cell e.g. If a cell in a data validated column has "Architect" the first number generated would be "Arch001", if "Supplier" the first number would be "Supp001" & if subsequently down the column "Architect" is used again this would create "Arch002", any assistance with this would be greatly appreciated, thank you
could you supply a sample file with some dummy data it it please?
sounds like a left(cell-ref,4)&row()+?? would work though?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi FDibbins,
Please see attached, basically I need to reference column "M" which has contractor names data validated, if "ApexFab" is used I require column "A" to produce a unique ID e.g. "AF-0001" & any further entries using the same company would result in "AF-002", "AF-003" respectively, if however the next entry is "LewisSteel" I require the ID to be relative to the company e.g. "LS-001" numbered exclusively to each company, hope this makes sense FDibbins? If not I will endeavour to clarify further ok...thank you for your time
If you want use short names for a supplier eg: AF for "ApexFab" one way is,
In list tab, create 2 column table. firts column enter the suppliers names & 2nd column enter their short name. then,
In A2,
=IFERROR(VLOOKUP(M3,Table,2,0)&TEXT(COUNTIF(M$3:M3,M3),"-0000"),"")
then copy down.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Hi Haseeb,
Can't get this code to work, sadly my prowess in Excel is far below yours, could you update the spread sheet to show these changes? Thank you again for your time Haseeb, greatly appreciated,
Hi matrixpom,
See the attached. On this currently generate ID for entry from 'Supplier' group. if you want to include more add them in 'ShortNames' range.
Re: How to generate a unique ID
Hi Haseeb,
That works perfectly! You're a genius! Thank you, Marco
please disregard my post here. sorry for the inconvenience
Last edited by hannahvan; 11-14-2014 at 02:48 PM.
Hi, welcome to the forum hannahvan
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Hi,
I want to create a unique reference number for every email i update in my excel spreadsheet. Example: 150130-001, 002 and so on.. for every new row i insert. The first 6 numbers will represent the date and the last 3 will be the sequence number of the email received that day. I tried with few formula's, but was unable to arrive at an appropriate solution.
Note that the process includes inserting a new row on every single entry. I made this way so that i can see the most recent email on the top. Please can anyone help me. Thanks!
Regards...Vish.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks