Counting Criteria Values with For Loop

[mlthornton]

I'm stuck trying to figure out the right method for this:

In A1:A10 I have:
Smith
Smith
Smith
Jones
Jones
Jones
Jones
Taylor
Taylor
Taylor

In B1:B10 I have:
A
B
A
B
B
A
C
A
A
C

I'm attempting to count the number of A's, B's, and C's for each last name (Smith, Jones, Taylor) to make sure there are 2 of each letter for each last name. If there are more or less than 2 it displays that in Col C for each last name. Is a For loop and Nested Ifs the right way to go about this?

Sub TestCode()

CountAVals = 0
CountBVals = 0
CountCVals = 0

For Each N In Range("A1:A7")

If Cells(N.Row, 2) = "A" Then
CountAVals = CountAVals + 1
ElseIf Cells(N.Row, 2) = "B" Then
CountBVals = CountBVals + 1
ElseIf Cells(N.Row, 2) = "B" Then
CountCVals = CountCVals + 1
End If

'reset CountVals when column A value changes
If Cells(N.Row + 1, 1) <> N Then
If CountAVals < 2 Then
Cells(N.Row, 3) = "missing A Vals"
ElseIf CountAVals > 2 Then
Cells(N.Row, 3) = "too many A Vals"
End If
If CountBVals < 2 Then
Cells(N.Row, 3) = "missing B Vals"
ElseIf CountBVals > 2 Then
Cells(N.Row, 3) = "too many B Vals"
End If
If CountCVals < 2 Then
Cells(N.Row, 3) = "missing C Vals"
ElseIf CountCVals > 2 Then
Cells(N.Row, 3) = "too many C Vals"
End If
CountAVals = 0
CountBVals = 0
CountCVals = 0
End If

Next N

End Sub

 

[Claus Busch]

Hi,

Am Mon, 5 Jan 2015 02:35:21 -0800 (PST) schrieb (e-mail address removed):

I'm stuck trying to figure out the right method for this:

In A1:A10 I have:
Smith
Smith
Smith
Jones
Jones
Jones
Jones
Taylor
Taylor
Taylor

In B1:B10 I have:
A
B
A
B
B
A
C
A
A
C

I'm attempting to count the number of A's, B's, and C's for each last name (Smith, Jones, Taylor) to make sure there are 2 of each letter for each last name. If there are more or less than 2 it displays that in Col C for each last name. Is a For loop and Nested Ifs the right way to go about this?

what Excel version do you use?
xl2007 or later you can use COUNTIFS:
=COUNTIFS(A1:A10,"Smith",B1:B10,"A")
(for Smith and A)
In earlier version try:
=SUMPRODUCT(--(A1:A10="Smith"),--(B1:B10="A"))

You could also try a Pivot table. Insert a row for headers. Insert =>
Pivot table and drag the names to the row field, the criteria to column
field and values.


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Mon, 5 Jan 2015 02:35:21 -0800 (PST) schrieb (e-mail address removed):

I'm attempting to count the number of A's, B's, and C's for each last name (Smith, Jones, Taylor) to make sure there are 2 of each letter for each last name. If there are more or less than 2 it displays that in Col C for each last name. Is a For loop and Nested Ifs the right way to go about this?

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "CountingCriteria"
It is a formula solution and the values will be modified if source table
is changed.


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Mon, 5 Jan 2015 12:24:01 +0100 schrieb Claus Busch:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

I also made a VBA solution. You have to download the file because macros
are disabled in OneDrive.


Regards
Claus B.