formula or macro.
Hi,
the problem is need a formula or a macro for my excel 2003 work sheet. this is part of my work sheet below: column column A B ------------------------------- 5 2 6 2 dog 1 dog 2 dog 1 that I want is, how to create a formula or macro to chane ( if column A is dog, column B at the same row replace to T01 and by order(T02,T03) like sample below column column A B ------------------------------- 9 2 10 2 dog T01 dog T02 dog T03 Regards Johnny. |
formula or macro.
Assuming you don't have more than 100 consecutive occurrences of a
given item, the formula below will work. =IF(ISBLANK(A2),"",IF(A2<A1,"T01","T" & TEXT(RIGHT(B1,2)+1,"00"))) If you have more than 100 consecutive occurrences, an If statement can be incorporated to check the length of the cell above and proceed accordingly. Johnny wrote: Hi, the problem is need a formula or a macro for my excel 2003 work sheet. this is part of my work sheet below: column column A B ------------------------------- 5 2 6 2 dog 1 dog 2 dog 1 that I want is, how to create a formula or macro to chane ( if column A is dog, column B at the same row replace to T01 and by order(T02,T03) like sample below column column A B ------------------------------- 9 2 10 2 dog T01 dog T02 dog T03 Regards Johnny. |
formula or macro.
Sub CountDogs() LastRow = Cells(Rows.Count, "A").End(xlUp).Row DogCount = 1 For RowCount = 1 To LastRow If Cells(RowCount, "A") = "dog" Then Cells(RowCount, "B") = "T" & Format(DogCount, "0#") DogCount = DogCount + 1 End If Next RowCount End Sub "JW" wrote: Assuming you don't have more than 100 consecutive occurrences of a given item, the formula below will work. =IF(ISBLANK(A2),"",IF(A2<A1,"T01","T" & TEXT(RIGHT(B1,2)+1,"00"))) If you have more than 100 consecutive occurrences, an If statement can be incorporated to check the length of the cell above and proceed accordingly. Johnny wrote: Hi, the problem is need a formula or a macro for my excel 2003 work sheet. this is part of my work sheet below: column column A B ------------------------------- 5 2 6 2 dog 1 dog 2 dog 1 that I want is, how to create a formula or macro to chane ( if column A is dog, column B at the same row replace to T01 and by order(T02,T03) like sample below column column A B ------------------------------- 9 2 10 2 dog T01 dog T02 dog T03 Regards Johnny. |
formula or macro.
Guys
the macro is working great. thank you. regards Johnny. "Joel" wrote: Sub CountDogs() LastRow = Cells(Rows.Count, "A").End(xlUp).Row DogCount = 1 For RowCount = 1 To LastRow If Cells(RowCount, "A") = "dog" Then Cells(RowCount, "B") = "T" & Format(DogCount, "0#") DogCount = DogCount + 1 End If Next RowCount End Sub "JW" wrote: Assuming you don't have more than 100 consecutive occurrences of a given item, the formula below will work. =IF(ISBLANK(A2),"",IF(A2<A1,"T01","T" & TEXT(RIGHT(B1,2)+1,"00"))) If you have more than 100 consecutive occurrences, an If statement can be incorporated to check the length of the cell above and proceed accordingly. Johnny wrote: Hi, the problem is need a formula or a macro for my excel 2003 work sheet. this is part of my work sheet below: column column A B ------------------------------- 5 2 6 2 dog 1 dog 2 dog 1 that I want is, how to create a formula or macro to chane ( if column A is dog, column B at the same row replace to T01 and by order(T02,T03) like sample below column column A B ------------------------------- 9 2 10 2 dog T01 dog T02 dog T03 Regards Johnny. |
All times are GMT +1. The time now is 11:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com