M

#### mlthornton

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