Assign unique project code

I "assume" you will hold a "central" list of all current project names. What happens if the first 5 characters of two different companies are the same ConstructionTech vs ConstructionWhizzo ?

See yellow banner above on how to attach a sample workbook.

Hey AliGW

I've tried to reply but getting the following error:

The following errors occurred with your submission
You are not allowed to post any kinds of links, images or videos until you post a few times.

My reply to John has no links, images or video so not sure what the issue is if you could please assist?

Cheers, Brendan

Hi John

I believe I covered this scenario in my initial post however I have attached a sample spreadsheet now which should make more sense.

Cheers

Hey Ali

I'm putting it down to a system glitch, my password suddenly wasn't working either!

Password reset and managed another reply so we'll just move on :D

Hi Diana, I appreciate your reply.

That sort of works but I my testing didn't bring about the output I intended (unless I was perhaps doing something incorrect. I've attached a spreadsheet showing your formulas applied to a list of random company names and the output your formulas brought about vs the expected values.

This is the formula I've got so far where column A is the company name and column B is the 'should-be' code if the name wasn't already taken. This would work similar to your method where there's a helper column, in my case column A is the company name and column B is the "should be" code that would be the code if not already used.

=IF(COUNTIF($B$2:$B3,$B4),IF(ISERR(RIGHT(B4,1)*1),LEFT(B4,LEN(B4)-1)&"0",IF(RIGHT(B4,2)="99",LEFT(B4,LEN(B4)-2)&"000",IF(RIGHT(B4,1)="9",LEFT(B4,LEN(B4)-2)&"00",LEFT(B4,LEN(B4)-1)&B4+1))),B4)

This formula (although not quite working) should in theory:

1. 1 Look to see if the 5 digit code created in column B has been used already
2. 2 If it hasn't then use that code - easy!
3. 3 If the code has already been used then:
4. If code ends in anything other than a number, drop the last digit and replace with a '0'
5. If the code ends in '99' then replace last 2 digits with '000'
6. If code ends in a '9' then replace the last 2 digits with '00'
7. If code ends in any other number then add 1 to the final number (eg 3 becomes a 4)

I haven't quite got this code to work but where I've got no idea is how to apply repeatability. The range lookup should repeat such that if the output code is found in the range it provides another ID, and if that ID is found in range, apply another ID and so on.

Regards, Brendan

Slight mod to Diana's formula:


=IF(LEN(A2)<5,LEFT(A2,5)&REPT(0,4-LEN(A2))&B2,IF(B2>=1,LEFT(A2,4)&(B2-1),LEFT(A2,5)))


Change to helper

=SUMPRODUCT(--(LEFT($A$2:A2,5)=LEFT(A2,5)))-1