Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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

excel
MS Office Version: Office 365
posted by: AmitJain on Fri Aug 19, 2016 at 2:58 pm
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)

posted by: queue (rep: 467) on Mon Aug 22, 2016 at 12:45 pm
Discuss


Answer the Question

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