COUNTIF TO BUILD SIMPLE CODE

[Guest]

Can anyone help me !

Hyaden HY01
Hillary HI01
Hilman HI02
Hilcrest HI03
Hyath HY02

I want a function to find the next first 2 charecters and give incremental
number with format of zero zero (ok the names are not repeating, they are
unique)

Now i pic first 2 letters to another column, countif then put incremental
number

=mid(a3,1,2) in C3
=countif(c$3:c3,c3) in d3
=c3&text(d3,"00")

is there any function (single or compound) to get HY02 without wasting 2
columns

Thanks in advance

 

[Bernie Deitrick]

Eddy,

=UPPER(LEFT(A3,2))&TEXT(COUNTIF(A$3:A3,LEFT(A3,2)& "*"),"00")

HTH,
Bernie
MS Excel MVP

 

[Guest]

=LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&"*"),"00")

 

[Guest]

Hi,

this was so helpful and works like charm.

thanks again.

 

[Guest]

Hi,

In the same query, if i have to check the whole string for repeat and if
that search is a NOT-REPEAT one then coding should be with first 2letter of
the search string trimed with number increment in the format ZERO ZERO.
If the search string is repeat one then no coding or show "repeat"

customer name in a:a and this function to be placed in b3 (copied down the
rows)

thanks in advance please.

 

[Bernie Deitrick]

Eddy,

=IF(COUNTIF(A$3:A3,A3)>1,"Repeat",UPPER(LEFT(A3,2))&TEXT(COUNTIF(A$3:A3,LEFT(A3,2)&
"*"),"00"))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,
thank you.

Eddy,

=IF(COUNTIF(A$3:A3,A3)>1,"Repeat",UPPER(LEFT(A3,2))&TEXT(COUNTIF(A$3:A3,LEFT(A3,2)&
"*"),"00"))

HTH,
Bernie
MS Excel MVP