count names

[EXCELMACROS]

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.

 

[Bob Phillips]

Try

=COUNTIF(A1:A6,"<>AAA")+(A6="AAA")

HTH

Bob

 

[EXCELMACROS]

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?

 

[Bob Phillips]

I didn't allow a for row 1 being blank

=COUNTIF(A1:A6,"<>AAA")+(A6="AAA")-(A1<>"AAA")

HTH

Bob

 

[EXCELMACROS]

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

 

[Rick Rothstein]

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)

 

[Héctor Miguel]

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.

 

[Bob Phillips]

Every time your examples change the requirement. If you want a full
solution, give us a full requirement.

Bob

 

[Robert Crandal]

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 are: " & 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
=============================================================

 

[EXCELMACROS]

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.

 

[Lars-Åke Aspelin]

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