Counting Criteria Values with For Loop

  • Thread starter Thread starter mlthornton
  • Start date Start date
M

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
 
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?
Click to expand...

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.
 
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?
Click to expand...

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.
 
Back
Top