Unique Batch ID

0

Hi,

Please suggest a formula to create a unique batch ID based on three value.

I have excel sheet where

Column A = Department name (HR, OPS)

Column B=Date 

Now i want unique ID based on Column A & B and unique no. i.e HR-Aug-16-1

there is a condition when count of combination of column a & B reached 50 unique value (last digit of ID) will change 2 and on next 50, will change by 3 so on.

right now i am using formula =&A2&"-"&text(b2,"mmm-yy"&1 but its not solve the purpose.

Kindly suggest.

Amit Jain

Answer
Discuss

Answers

0

I would attempt to keep formatting consistant.  I started in column e16 with the following formula:

=A16&YEAR(B16)&TEXT(MONTH(B16),"00"&TEXT(DAY(B16),"00"))&TEXT(C15+1,"00")

[ the formatting allows for 2 digit month and day; then I increment by 1 to previous cell in Column C (you could put a formula in col C to take a number and add '1' to it)

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login