|
count names
hi all€¦. Im trying to count the number of times a name is on consecutive
cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... |
count names
Try
=COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... |
count names
Sorry Bob, it does not work. You can try it by changing the places for
example: A1 = blank A2 = AAA A3 = AAA A4 = AAA A5 = blank A6 = AAA Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and group 2 for a6) Thank youf or replying, any other ideas? -- Thank you... "Bob Phillips" wrote: Try =COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... . |
count names
I didn't allow a for row 1 being blank
=COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA") HTH Bob "EXCELMACROS" wrote in message ... Sorry Bob, it does not work. You can try it by changing the places for example: A1 = blank A2 = AAA A3 = AAA A4 = AAA A5 = blank A6 = AAA Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and group 2 for a6) Thank youf or replying, any other ideas? -- Thank you... "Bob Phillips" wrote: Try =COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... . |
count names
Hi Bob, sorry if i'm not explaining the situation.... your formula does not
work if we change the scenario, I did, A1 = Blank A2 = Blank A3 = Blank A4 = AAA A5 = blank A6 = AAA and your formula gives me a 4, i'm expecting a 2. Just to give you more information, each cell is a day of the month and the name is a person who will perform a dutie. I want to know at the end of the month how many times 1 person did two or more days in a row. At the end it could be random so a person might perform a function 3 days in a row and I want to count that as 1 frequency.... make sense? and I expct -- Thank you... "Bob Phillips" wrote: I didn't allow a for row 1 being blank =COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA") HTH Bob "EXCELMACROS" wrote in message ... Sorry Bob, it does not work. You can try it by changing the places for example: A1 = blank A2 = AAA A3 = AAA A4 = AAA A5 = blank A6 = AAA Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and group 2 for a6) Thank youf or replying, any other ideas? -- Thank you... "Bob Phillips" wrote: Try =COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... . . |
count names
Somehow, I can't help feeling you have simplified your question for this
newsgroup and the your real question is more complex than this. Is a UDF (user defined function) solution okay? If so, then the following UDF will do what your original posting asked for, namely, count groupings of the "name" AAA in the Column 1 (although I did design the UDF to allow you to specify an alternate column is desired)... Function CountAAAGroups(Optional Col As Variant = "A") As Long On Error Resume Next CountAAAGroups = 1 + UBound(Split(WorksheetFunction.Trim(Replace(Join( _ WorksheetFunction.Transpose(Intersect(Columns(Col) , _ ActiveSheet.UsedRange))), "A ", "A")))) End Function If you have never worked with UDFs, you would install the above by pressing Alt+F11 to go to the Visual Basic editor, then click Insert/Module from its menu bar and copy/paste the above code into the code window that opened up. To use the UDF, just call it like any other Excel worksheet function; for example, enter this in any cell... =CountAAAGroups() If you wanted to specify a column other than Column A, you can do so one of two ways (let's use Column E as our example); like this... =CountAAAGroups("E") or like this =CountAAAGroups(5) -- Rick (MVP - Excel) "EXCELMACROS" wrote in message ... Hi Bob, sorry if i'm not explaining the situation.... your formula does not work if we change the scenario, I did, A1 = Blank A2 = Blank A3 = Blank A4 = AAA A5 = blank A6 = AAA and your formula gives me a 4, i'm expecting a 2. Just to give you more information, each cell is a day of the month and the name is a person who will perform a dutie. I want to know at the end of the month how many times 1 person did two or more days in a row. At the end it could be random so a person might perform a function 3 days in a row and I want to count that as 1 frequency.... make sense? and I expct -- Thank you... "Bob Phillips" wrote: I didn't allow a for row 1 being blank =COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA") HTH Bob "EXCELMACROS" wrote in message ... Sorry Bob, it does not work. You can try it by changing the places for example: A1 = blank A2 = AAA A3 = AAA A4 = AAA A5 = blank A6 = AAA Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and group 2 for a6) Thank youf or replying, any other ideas? -- Thank you... "Bob Phillips" wrote: Try =COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... . . |
count names
hi, (?)
... trying to count the number of times a name is on consecutive cells... example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups first group 2 times and second group 3 times... (i.e.) =sumproduct(--(a1:a6="aaa"),--(a1:a6<a2:a7)) hthm hector. |
count names
Every time your examples change the requirement. If you want a full
solution, give us a full requirement. Bob "EXCELMACROS" wrote in message ... Hi Bob, sorry if i'm not explaining the situation.... your formula does not work if we change the scenario, I did, A1 = Blank A2 = Blank A3 = Blank A4 = AAA A5 = blank A6 = AAA and your formula gives me a 4, i'm expecting a 2. Just to give you more information, each cell is a day of the month and the name is a person who will perform a dutie. I want to know at the end of the month how many times 1 person did two or more days in a row. At the end it could be random so a person might perform a function 3 days in a row and I want to count that as 1 frequency.... make sense? and I expct -- Thank you... "Bob Phillips" wrote: I didn't allow a for row 1 being blank =COUNTIF(A1:A6,"<AAA")+(A6="AAA")-(A1<"AAA") HTH Bob "EXCELMACROS" wrote in message ... Sorry Bob, it does not work. You can try it by changing the places for example: A1 = blank A2 = AAA A3 = AAA A4 = AAA A5 = blank A6 = AAA Your formula gives me 3 and i expect 2 (2 groups, group 1 for a2 to a4 and group 2 for a6) Thank youf or replying, any other ideas? -- Thank you... "Bob Phillips" wrote: Try =COUNTIF(A1:A6,"<AAA")+(A6="AAA") HTH Bob "EXCELMACROS" wrote in message ... hi all.. I'm trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. -- Thank you... . . |
count names
I created a function below that should give you what you want.
You need to give the function a row value ("iRow"), how many cells or columns to count ("n"), and tell it which name or string to search for ("token"). So, you could use it like follows: MsgBox "Total AAA's in Row1 a " & CountRowTokens(1, 6, "AAA") Since it's also a function, you could probably use it as a formular in the actual spreadsheet as well. ================================================== ====================== Public Function CountRowTokens(ByVal iRow As Integer, ByVal n As Integer, ByVal token As String) Dim i, cState, iState, nFull As Integer If (Cells(iRow, 1).Value = token) Then nFull = 1 cState = 1 Else nFull = 0 cState = 0 End If For i = 2 To n If (Cells(iRow, i).Value = token) Then iState = 1 Else iState = 0 End If If (iState < cState) Then If (iState = 0) Then cState = 0 Else cState = 1 nFull = nFull + 1 End If End If Next i CountRowTokens = nFull End Function ================================================== =========== |
count names
Thank you Robert, Hector, Bob and Rick, I'm going to test out your options
tonight and will give you the results tomorrow. SORRY for not being more specific and yes, I'm familiar with VB. -- Thank you... "Robert Crandal" wrote: I created a function below that should give you what you want. You need to give the function a row value ("iRow"), how many cells or columns to count ("n"), and tell it which name or string to search for ("token"). So, you could use it like follows: MsgBox "Total AAA's in Row1 a " & CountRowTokens(1, 6, "AAA") Since it's also a function, you could probably use it as a formular in the actual spreadsheet as well. ================================================== ====================== Public Function CountRowTokens(ByVal iRow As Integer, ByVal n As Integer, ByVal token As String) Dim i, cState, iState, nFull As Integer If (Cells(iRow, 1).Value = token) Then nFull = 1 cState = 1 Else nFull = 0 cState = 0 End If For i = 2 To n If (Cells(iRow, i).Value = token) Then iState = 1 Else iState = 0 End If If (iState < cState) Then If (iState = 0) Then cState = 0 Else cState = 1 nFull = nFull + 1 End If End If Next i CountRowTokens = nFull End Function ================================================== =========== . |
count names
On Fri, 5 Feb 2010 04:20:01 -0800, EXCELMACROS
wrote: hi all…. I’m trying to count the number of times a name is on consecutive cells. Sounds easy but I can't find a function for this, maybe a little macro that goes cell by cell on each row? I appreciate your help, for example: A1 = AAA A2 = AAA A3 = blank A4 = AAA A5 = AAA A6 = AAA Then my results should be equal to 2 since the name AAA appear in two groups, first group 2 times and second group 3 times. Try this formula: =SUMPRODUCT(--((A1:A6)="AAA"),--((A2:A7)<"AAA")) Hope this helps / Lars-Åke |
| All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com