Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Criteria Values with For Loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Criteria Values with For Loop
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Criteria Values with For Loop
Hi,
Am Mon, 5 Jan 2015 02:35:21 -0800 (PST) schrieb : 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=9378A...121822A3%21326 for "CountingCriteria" It is a formula solution and the values will be modified if source table is changed. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Criteria Values with For Loop
Hi,
Am Mon, 5 Jan 2015 12:24:01 +0100 schrieb Claus Busch: https://onedrive.live.com/?cid=9378A...121822A3%21326 I also made a VBA solution. You have to download the file because macros are disabled in OneDrive. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help on either doing a loop and counting specific values orusing subtotal function | Excel Worksheet Functions | |||
Counting with two criteria where one is between values | New Users to Excel | |||
Counting unique values with criteria | Excel Worksheet Functions | |||
Counting unique values with criteria | Excel Discussion (Misc queries) | |||
Counting Unique Values Given Criteria | Excel Worksheet Functions |