Generate unique ID based on criteria from different columns

[cherias]

How to create a unique ID based on criteria's from different columns. The formula should auto assign a new unique ID when a new entry is added in the manner :

1) Assign a new & unique ID (next in series), if the description is new and not repeated earlier in the list. The new unique ID should consider the Risk Rating as well

2) If the description is repeated, then assign the same unique ID as assigned to its predecessor.

Attached a sample raw data.

Unique ID Raw Data.JPG

[kersplash]

2) If the description is repeated, then assign the same unique ID as assigned to its predecessor.

Then it will no longer be unique.

[cherias]

@kersplash : Yes I understand that logic however my intent was that is a specific description is added to the list, then it will get the same ID that is already assigned to the description. For example, if Description 2 is added to the list, then the new entry will get the unique ID :- Risk ID_2_M

[kersplash]

Try this in D2 and copy down;

Please Login or Register  to view this content.

[cherias]

Hello Kesplash,

While in dummy raw data, the description is provided as " Description 1" .. "Description 2" ..... but in actual the real data would have a sentence or some text form and wont be so organised. So when I replaced the Description 1.. 2... with an actual text, the formula doesn't work. Please refer to the screen shot below.

Kindly assist, on how to modify the formula if a text/sentence was there in Risk Description.

Attachment 625557

[JeteMc]

Attempting to open the attachment to post #5 prompts the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
Please upload a sample file (as in post #1) that demonstrates the type text that will be included in the Risk Description column instead.
Remember to include expect results in the Unique ID column.
Let us know if you have any questions.

[cherias]

Hello JeteMc,

As suggested, I have attached the file. I had done it earlier as well but for some reason its now missing. The expected results are already shown in screenshot and file attached in the first post. Please check and let know, if its fine or I can reattach.

Thank You

[Bo_Ry]

Please try at E2
="Risk ID_"&MATCH(C2,C$2:C$25,)&"_"&LEFT(B2)

[cherias]

Thank you Bo_Ry, this works as intended. Awesome and thanks a lot for the help.