Excel Auto Create Unique Numeric Number

[knm1872]

Hello
I'm a total novice excel user but have a need at work that I'm hoping we can use excel to solve.
We need to create one place where 3 separate people can log applications taken from our customers - excel workbook.
To that end, they all need a unique identifier that we don't want them to generate or edit. We are hoping we can use excel to auto generate it based on information they put into the spreadsheet. This unique ID will be used to later import into another system.
We are attempting to eliminate duplicates into the future (over at least 2 calendar years).
I'm putting in a sample of what the spreadsheet can look like (there would be more columns with additional data but none would needed for this exercise).
Any help, ideas, suggestions, solutions you all could propose would be much appreciated. I'm in a bit over my head but am hoping excel can do something like this.
Thanks in advance,
Kathie

[TABLE="width: 434"]
<colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="240" style="width: 180pt; mso-width-source: userset; mso-width-alt: 8777;"> <tbody>[TR]
[TD="class: xl66, width: 142, bgcolor: yellow"]Unique ID[/TD]
[TD="class: xl65, width: 89, bgcolor: transparent"]Date[/TD]
[TD="class: xl65, width: 107, bgcolor: transparent"]LOB[/TD]
[TD="class: xl65, width: 240, bgcolor: transparent"]Customer Name[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"]LYYYYMMDDxxxxxx[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/9/2018[/TD]
[TD="class: xl63, bgcolor: transparent"]Mortgage[/TD]
[TD="class: xl63, bgcolor: transparent"]Doe, John[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/10/2018[/TD]
[TD="class: xl63, bgcolor: transparent"]Consumer[/TD]
[TD="class: xl63, bgcolor: transparent"]Smith, Sally[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/11/2018[/TD]
[TD="class: xl63, bgcolor: transparent"]Commercial[/TD]
[TD="class: xl63, bgcolor: transparent"]ABC Corporation[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]L = LOB [/TD]
[TD="bgcolor: transparent"]1=Mortgage[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]2=Consumer[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]3=Commercial[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]YYYYMMDD[/TD]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxxxx[/TD]
[TD="bgcolor: transparent, colspan: 2"]Auto generated Unique ID[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, colspan: 3"]Auto generate and lock field for changes.[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[knm1872]

okay so I came up with 3 formulas that work in 3 different cells...can anyone help me combine them into one? I've given up on the YYYYMMDD and just went with year.

=IF(D2="Mortgage",1,IF(D2="Consumer",2,3)) -- this gets me the prefix of 1,2 or 3
=B14&2018 -- this takes the result of line 1 above and adds the year
=C14&RANDBETWEEN(1,9999) -- this adds the random number at the end.


I'm assuming, I can just leave it at 3 columns, hide them and protect them. The users will never know. But it would be nicer to have in one cell.

For those taking a look, thank you. Appreciate any help you can give.

 

[Charlie45]

Try putting them all together

=IF(D2="Mortgage",1,IF(D2="Consumer",2,3))&2018&C14&RANDBETWEEN(1,9999)

 

[Dryver14]

Be aware Randbeween function does not guarantee unique numbers

 

[knm1872]

Thank you...that worked!!

But as most things do...I have more questions.
How do I get it to stop changing the number of the previous entries each time one is created? We need to take this unique ID once it's created and put it into another system for reporting.
For example, when I add more lines to the spreadsheet, it changes the numbers above it (the RAND ones).

 

[knm1872]

Thank you....that could be a problem. I really need to get the entire date YYYYMMDD to appear, then I think we will be okay.
We are a bank, we need to keep loan application data for government required HMDA reporting. All lines of business use different systems, so we need one repository for tracking loans not generated. I know blah, blah, blah. We feel the same way.
Within one calendar year (reporting cycle), we will more than likely not have the same borrower make application on the same day for the same product/loan amount. These are additional columns on the spreadsheet. We are a relatively small bank and do not generate lots of loan applications that don't result in loans.
Our plan was to use share this workbook to log all applications that don't generate loans. The unique ID assures we won't match any other generated number in other systems. Either loan numbers which won't match this sequence at all. Then we can use this log, along with others from the loan systems, to upload for government reporting.

 

[dave2018]

in A2 type =IF(C2="Mortgage",1,IF(C2="Consumer",2,3))&TEXT(B2,"yyyymmdd")&TEXT(ROWS($B$2:B2),"00000")

this will put in the year dynamically and create sequential numbers from 00001 and onwards

 

[wideboydixon]

How about something like this:

Book1
	A	B	C	D
1	Unique ID	Date	LOB	Customer Name
2	120180309000755	09/03/2018	Mortgage	Doe, John
3	220180310001110	10/03/2018	Consumer	Smith, Sally
4	320180311001398	11/03/2018	Commercial	ABC Corporation
Sheet1

Cell Formulas
Range	Formula
A2	=VLOOKUP($C2,{"Mortgage",1;"Consumer",2;"Commercial",3},2,FALSE)&TEXT($B2,"yyyymmdd")&TEXT(SUMPRODUCT(CODE(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1))),"000000")

WBD

 

[knm1872]

Thank you so much!!
You have no idea how much this will help us. Again, can't thank you enough.

 

[knm1872]

Thank you so much.
You people are geniuses!! I need excel classes. lol
Again, can't thank you enough. Really. This will make our lives so much easier.