Prevent duplicates in creating client reference codes

[brianfosterblack]

I need to create a client reference code for each of my clients using this formula
=IF(B3="","",UPPER("C"&LEFT(C3,3)&G3&"001"))
This is done in column A
However if the formula creates a duplicate reference code it needs to make the last number 002 or 003 or 004 until the result is unique
Is there a way to do this in a formula?

 

[hajiali]

Try

=IF(B3="","",UPPER("C"&LEFT(C3,3)&G3&"00"&COUNTIFS($B$3:B3,B3,$G$3:G3,G3,$C$3:C3,C3)))

the above counts if columns B,C and G are the same.

 

[brianfosterblack]

Try

=IF(B3="","",UPPER("C"&LEFT(C3,3)&G3&"00"&COUNTIFS($B$3:B3,B3,$G$3:G3,G3,$C$3:C3,C3)))

the above counts if columns B,C and G are the same.

Thanks but this does not work. In my client list (column C) in rows 13 and 14 I have 2 clients
Creighton & Associates and
Creighton Gauteng
My clients list in column C only lists unique names but as you see if the first 3 letters are the same I am ending up with the same code
what I need is if I have a duplication of the first 3 letters in column C then the second one must be 002 the third 003 and so on. I do not think there will ever be more than 5 duplicates as my list is currently only coming up with 2 instances. Column B will never have duplicates and the duplicates in column G do not matter as these can have dozens of duplicates.

 

[hajiali]

I would suggest using a helper Column and try this

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8
9
10
11
12
13	CCRE3001	A	Creighton & Associates				3	Cre
14	CCRE4002	A	Creighton Gauteng				4	Cre
Sheet1

Cell Formulas
Range	Formula
A3:A14	A3	=IF(B3="","",UPPER("C"&H3&G3&"00"&COUNTIF($H$3:H3,H3)))
H3:H14	H3	=LEFT(C3,3)

 

[Fluff]

How about

=IF(B3="","",UPPER("C"&LEFT(C3,3)&G3&TEXT(SUMPRODUCT((LEFT(B$3:B3,3)=LEFT(B3,3))*(G$3:G3=G3)),"000")))

 

[brianfosterblack]

Thanks this one works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.