Unique Number

[nnadimi19]

I have a column that has check numbers (20,000 check numbers) I need to create another column that assigns a unique refrence number for each check. The requierment is that it has to be exactly 32 characters long and it can have a combination of alphabets and numbers. The alphabets have to be in upper case.

example

check Number unique number
12345 A0124578896333347852124758589621
65879 D0124578896333347852124758501589


any help will be highly appreciated. ray: ray: ray:

 

[PA HS Teacher]

1. Should the new reference numbers follow a pattern or be completely random?

2. Do they only need to be unique out of the 20,000 check numbers in your list, or do we need to ensure that future check numbers will have a unique alphnumeric code as well. (in which case I would think we want to have a system)

3. In your example, I noticed you had a few alphabetic characters in the beginning followed by numbers. Is this desired, or would Alphabetic characters throughout be acceptable?

 

[nnadimi19]

Thanks for the quick response.

The answer to your questions are:

1) they can completely be random.
2) We need to have a system because they will be other checks besides the 20,000.
3) Alphanumeric numers through out will beacceptable.

 

[nnadimi19]

Can someone please ray: ray: help me on this, I am in a very desperate situation for a solution as need something done ASAP

 

[Jeggburt]

Are your check numbers going to be unique themselves?

Also, do the check numbers have a certain limit on how many characters are within them?

 

[nnadimi19]

The check numbers can be unique but they do not have to, the reference number for each check shoud be unique though, and they need to be 32 characters long and be alphanumeric.

 

[Peter_SSs]

nnadimi19

Would this fit the requirements? B2 (copied down) is:
=REPT("0",5-LEN(ROW()))&ROW()&REPT("A",27-LEN(A2))&A2

Mr Excel.xls
	A	B	C	D
1	Check No	Unique No
2	12345	00002AAAAAAAAAAAAAAAAAAAAAA12345
3	65879	00003AAAAAAAAAAAAAAAAAAAAAA65879
4	23	00004AAAAAAAAAAAAAAAAAAAAAAAAA23
5	1000000	00005AAAAAAAAAAAAAAAAAAAA1000000
6	12345	00006AAAAAAAAAAAAAAAAAAAAAA12345
7	8	00007AAAAAAAAAAAAAAAAAAAAAAAAAA8
8	8	00008AAAAAAAAAAAAAAAAAAAAAAAAAA8
9	6953	00009AAAAAAAAAAAAAAAAAAAAAAA6953
10	12345	00010AAAAAAAAAAAAAAAAAAAAAA12345
11
Unique Ref Nos

 

[goblin]

You might want to go with a proper hash of the number. Check out:

http://www.cryptosys.net/hash.html

 

[nnadimi19]

Peter;

Your formula works except for the fact that it is check number dependant and hence the reference number is not always 32 characters.

 

[Peter_SSs]

Peter;

Your formula works except for the fact that it is check number dependant and hence the reference number is not always 32 characters.

I believe it is. Here is my sheet again with C2 copied down:
=LEN(B2)

Mr Excel.xls
	A	B	C	D
1	Check No	Unique No	Unique No Length
2	12345	00002AAAAAAAAAAAAAAAAAAAAAA12345	32
3	65879	00003AAAAAAAAAAAAAAAAAAAAAA65879	32
4	23	00004AAAAAAAAAAAAAAAAAAAAAAAAA23	32
5	1000000	00005AAAAAAAAAAAAAAAAAAAA1000000	32
6	12345	00006AAAAAAAAAAAAAAAAAAAAAA12345	32
7	8	00007AAAAAAAAAAAAAAAAAAAAAAAAAA8	32
8	8	00008AAAAAAAAAAAAAAAAAAAAAAAAAA8	32
9	6953	00009AAAAAAAAAAAAAAAAAAAAAAA6953	32
10	12345	00010AAAAAAAAAAAAAAAAAAAAAA12345	32
11
Unique Ref Nos