Thrawn3000
asked on
Count the number of people in a table that meet a given target in consecutive months?
Hi experts
I need to use vba code to count the number of people that meet a target in consecutive months
example the target is 30 and above thus the total number of people that meet this target in consecutive months where the second month is 31/12/2010 is 2
Sam gets 50 in the first month and 45 in the second
peter gets 40 in the firts month and 40 in he second , thus 2 people meet the target in consecutive months.
The tabel itself cosnsist of over 10,000 records with over 2000 individuals is there a way to do this using vba?
Date Target Name
31/11/2010 50 Sam
31/11/2010 40 Dan
31/11/2010 40 Kent
31/11/2010 30 Peter
31/12/2010 45 Sam
31/12/2010 40 Kent
31/12/2010 25 Peter
31/01/2011 40 Chris
31/01/2011 10 Sam
I need to use vba code to count the number of people that meet a target in consecutive months
example the target is 30 and above thus the total number of people that meet this target in consecutive months where the second month is 31/12/2010 is 2
Sam gets 50 in the first month and 45 in the second
peter gets 40 in the firts month and 40 in he second , thus 2 people meet the target in consecutive months.
The tabel itself cosnsist of over 10,000 records with over 2000 individuals is there a way to do this using vba?
Date Target Name
31/11/2010 50 Sam
31/11/2010 40 Dan
31/11/2010 40 Kent
31/11/2010 30 Peter
31/12/2010 45 Sam
31/12/2010 40 Kent
31/12/2010 25 Peter
31/01/2011 40 Chris
31/01/2011 10 Sam
ASKER
Thanks akoster
I have something similar to this working in my attached vba code, but given the size of the data
and number of names of consultants, please see tab wksCleandata I need to automate this in VBA
the tab wksDashboad shows where the sum of consultants who met a target for consecutive months should be, can this be simplified and automted so there uis no need to show distinct names.
Devel-Target-update5-automate.xlsm Devel-Target-update5-automate.xlsm
I have something similar to this working in my attached vba code, but given the size of the data
and number of names of consultants, please see tab wksCleandata I need to automate this in VBA
the tab wksDashboad shows where the sum of consultants who met a target for consecutive months should be, can this be simplified and automted so there uis no need to show distinct names.
Devel-Target-update5-automate.xlsm Devel-Target-update5-automate.xlsm
You can do it with formulas. Note that the formula in cell E2 is different than the one in E3. Not a single formula but a row of formulas. The final count is the number of sales people that had two consecutive months of sales greater than a target threshold. If a salesperson had three or more consecutive months o multiple instances of two consecutive months it is still counted as one instance.
Kevin
Q-26985135.xlsx
Kevin
Q-26985135.xlsx
ASKER
zorvek
that looks usefull can I alter this easily to pick up 3 consecutive months of targets hit or x consectuive months?
=IF(AND(B2>$F$1,COUNTIFS(A :A,DATE(YE AR(A2),MON TH(A2),0), C:C,C2,B:B ,">="&$F$1 )=1),1,0)
that looks usefull can I alter this easily to pick up 3 consecutive months of targets hit or x consectuive months?
=IF(AND(B2>$F$1,COUNTIFS(A
Here is a VBA script that should do what you need. You just need to change the TARGET_VALUE and INITIAL_MONTH fields and let it run.
sew
sew
Sub CountPeople()
Const TARGET_VALUE = 40
Const INITIAL_MONTH = "31/11/2010"
Set objDic = CreateObject("Scripting.Dictionary")
intTotalCount = 0
For intSrcRow = 2 To Cells(Cells.Rows.Count, "C").End(xlUp).Row
strCurDate = Cells(intSrcRow, "A").Value
strCurTarget = Cells(intSrcRow, "B").Value
strCurName = Cells(intSrcRow, "C").Value
If Not objDic.Exists(strCurName) And strCurDate = INITIAL_MONTH And strCurTarget >= TARGET_VALUE Then
objDic.Add strCurName, strCurName & "," & strCurTarget & "," & Day(DateSerial(Mid(strCurDate, 7, 4), Mid(strCurDate, 4, 2) + 1, 0)) & Mid(strCurDate, 3)
ElseIf strCurTarget >= TARGET_VALUE Then
If objDic.Item(strCurName) <> "" Then
aryPrevData = Split(objDic.Item(strCurName), ",")
strPrevName = aryPrevData(0)
strPrevTarget = aryPrevData(1)
strPrevDate = aryPrevData(2)
dtPrevDate = DateSerial(Mid(strPrevDate, 7, 4), Mid(strPrevDate, 4, 2), Left(strPrevDate, 2))
dtCurDate = DateSerial(Mid(strCurDate, 7, 4), Mid(strCurDate, 4, 2), Left(strCurDate, 2))
If DateDiff("m", dtPrevDate, dtCurDate) = 1 Then
intTotalCount = intTotalCount + 1
'MsgBox "ADDED: " & vbCrLf & FormatDateTime(dtCurDate, vbGeneralDate) & vbCrLf & FormatDateTime(dtPrevDate, vbGeneralDate)
End If
End If
End If
Next
MsgBox "Total found from " & INITIAL_MONTH & " >= " & TARGET_VALUE & " = " & intTotalCount
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is a three month solution with formulas. I removed the first row formula since it was not really needed.
Kevin
Q-26985135.xlsx
Kevin
Q-26985135.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
positive side is that it automatically calculates changes, drawback is that you have to type in all distinct names by hand and drag-expand the formula.
salesdata.xlsx