Excel2016: Generate ID based on multiple criteria (no VBA)

Question

I am trying to generate Batch ID based on Course, Date, & Time. All the rows which have the same Course+Date+Time combination should have the same Batch ID. All subsequent combinations should have incremental IDs

Batch ID = LEFT(C2,3)&TEXT(<code formula>,"000")

No VBA, only Excel 2016 formula, please.

Sample data snapshot

Answer 1

It would be easier and more readable to meet your requirement in three steps, rather than a single formula.

1. Create a unique ID based on the Course, Date and Time.

   Formula:

   =CONCATENATE(UPPER(LEFT($C3,3)),TEXT($D3,"ddmmyy"),TEXT($E3,"hhmm"))

   Breakdown:

   LEFT($C3,3) - take the first three characters of the Course

   UPPER() = make the first three characters of the Course uppercase

   TEXT($D3,"ddmmyy") = take the date, turn it into text and apply a format

   TEXT($E3,"hhmm") = take the time, turn it into text and apply a format

2. Create a lookup table of the unique ID and Batch ID

   Copy all the unique Ids that have been created in step 1

   Paste them into a new column separate to your data

   On the Data menu tab, select Remove Duplicates in the data tools

   Add the Batch ID to lookup.

   This way the Batch ID can be generate via formula if the Unique Id's are sorted using Sort A to Z.

   See the attached image.

3. Lookup the unique ID to get the Batch ID

   =VLOOKUP($F3,$I$3:$J$7,2,FALSE)