COUNTIF TO BUILD SIMPLE CODE

E

Eddy Stan

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
 
B

Bernie Deitrick

Eddy,

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

HTH,
Bernie
MS Excel MVP
 
E

Eddy Stan

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.
 
B

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
 
E

Eddy Stan

Hi Bernie,
thank you.


Bernie Deitrick said:
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
Click to expand...
 
Top