| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF TO BUILD SIMPLE CODE
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
Eddy,
=UPPER(LEFT(A3,2))&TEXT(COUNTIF(A$3:A3,LEFT(A3,2)& "*"),"00") HTH, Bernie MS Excel MVP "Eddy Stan" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
=LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&" *"),"00")
"Eddy Stan" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
Hi,
this was so helpful and works like charm. thanks again. "Teethless mama" wrote: =LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&" *"),"00") "Eddy Stan" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
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. "Teethless mama" wrote: =LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&" *"),"00") "Eddy Stan" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
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 "Eddy Stan" wrote in message ... 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. "Teethless mama" wrote: =LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&" *"),"00") "Eddy Stan" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF TO BUILD SIMPLE CODE
Hi Bernie,
thank you. "Bernie Deitrick" wrote: 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 "Eddy Stan" wrote in message ... 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. "Teethless mama" wrote: =LEFT(UPPER(A3),2)&TEXT(COUNTIF(A1:A3,LEFT(A3,2)&" *"),"00") "Eddy Stan" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple code for shifting | Excel Discussion (Misc queries) | |||
| Help with some simple code | Excel Discussion (Misc queries) | |||
| Simple Code, I think... | Excel Worksheet Functions | |||
| how to build simple inventory system | Excel Discussion (Misc queries) | |||
| Build in Code | Excel Worksheet Functions | |||
Linear Mode
