I am working on my company's sales database to create an unique ID for each sale (or project) base on the customer ID, product ID and sale date as the table below. These criteria are necessary to make sure that the same project has the same ID in all of our different spreadsheets so we can merge them. However, in cases where the same customer buys one or more of the same product in one day, the project ID end up being duplicated. I could not find a solution for this problem. Idealy (but not limited to) the formula should have some function to count how many of a certaing product the custumer have bought on that day and return the number (or index) of that project.

Current ID pattern: sale_Date (5 digits) + customer_ID (11 digits) + product_id (3 digits)

Ideal ID pattern: sale_Date (5 digits) + customer_ID (11 digits) + product_id (3 digits) + count of projects on that day (2 digits)

project_ID example table

Current ID formula used: =D2&TEXT(B2,"00000000000")&C2

Please let me know if you find a solution to this problem.

  • Add another column with dropdown numbers (e.g. 1 to 5). Prefill them with 1 but add conditional formatting to highlight a row in red if it doesn't have any number populated or if has the same as a duplicate for the same day then increment it. Or, instead of entering dates, enter date & time but use a number format to display only the date. You can then use the relative times of duplicates to dynamically add the extra digit. This assumes that you wouldn't insert a new time before an existing one after already sharing the project_ID. Both these approaches allow sorting without errors.
    – Blindspots
    Commented Jul 3 at 22:45
  • It seldom makes sense to encode information like dates, customer numbers or product numbers in a unique ID, let alone encoding information like the count of projects on the day. It is often easiest to add simple incrementing numbers, and use functions like vlookup() and filter() to locate the relevant date, customer and product. Commented Jul 4 at 8:41
  • Formula-based unique ID generators are prone to the ID changing when its source data is edited for some reason. To add permanent unique IDs that never change, you need a script. See my insertUniqueId_ script for sample code. Commented Jul 4 at 8:42


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.