Create a transaction ID from dataset in excel

[bongouni553]

Hi all
I need your assistance with the following on excel,

• The unique TransactionID consists of 13 characters. You need to create a function that will create this unique TransactionID.
• First four character: A combination of any four number of the alphabet (A-Z,a-z).
• Second group of four characters: The first four characters of the employeee number (0-9).
• Dash between the second and third group of characters.
• Next group of 3 characters: Random number between 100 and 999.
• Last number: Control number: The remainder when the last group of 3 numbers, is divided by 7. For example: 129 div 7 = 18 and the remainder is 4.
• I have for example employee number in one of my fields 1209090 in excel
• the transtacitonid should be like Abxy1209-1294
• I have a function with the following CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(65;90))&RANDBETWEEN(0;999) but its not working as desired .
• I need to also use the mod am stuck .

Thanks for your help

 

[etaf]

i'm hoping this is not homework

does
=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&LEFT(C1,4)&"-"&LET(r,RANDBETWEEN(0,999),r*1&"-"&MOD(r,7))

work for you
as you have excel 365 , you can use LET - so the random number is assigned to a variable and can be used more than once for the same number

129 / 7 = 18
18*7 = 126
remainder 3

Book1
	A	B	C	D	E	F	G
1	GKCY1209-591-3		1209090			UGRQ1209-743-1
2	375-4
3	3
Sheet1

Cell Formulas
Range	Formula
A1	A1	=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&LEFT(C1,4)&"-"&LET(r,RANDBETWEEN(0,999),r*1&"-"&MOD(r,7))
A2	A2	=LET(r,RANDBETWEEN(0,999),r*1&"-"&MOD(r,7))
A3	A3	=MOD(129,7)

 

[bongouni553]

This works form me
Thank you !!

 

[etaf]

you are welcome,
was this homework / assignment
as the wording in the question

You need to create a function that will create this unique TransactionID.

Implied it might be
if so, then thanks for at least having a go with the formula - a lot do not bother to try
I hope , if its homework, you fully understand why I used the LET ()