Hello Good People,
I've been working on a section of code for several days, am not finding any previous posts that are applicable to my situation and am desperately hoping someone is willing to undertake the challenge. I tried posting this problem last week but the responses I received were not applicable so I am trying again. I am not sure if this breaks any rules and I apologize if it does...I'm just, well, like I said, desperate.
I have ID numbers in col A, Dates in col B. The Start Date in cell C1 (1/1/2017)and the end date in Cell D1 (3/31/2017) - the end date is actually dynamic but I know how to address that.
I need to count the number of unique values in column A if the dates in column B are within the range. Here comes the tricky part...I only need to count them if there are 3 or more occurrences. For example: in this data, there is only 1 ID that appears within the date range of 1/1/17 to 3/31/17 three or more times. Although 14 and 503 both appear on the list at least 3 times, the occurrences are not between the date range. ID 414 is the only one with 3 or more occurrences within the stated date range.
My original spreadsheet used the array formula:"=SUM(IF(FREQUENCY(IF(('All Engagement Types'!$B$2:$B$10000>='Weekly BP & Engagement'!$C$1)*('All Engagement Types'!$B$2:$B$10000<='Weekly BP & Engagement'!$A15),MATCH('All Engagement Types'!$A$2:$A$10000,'All Engagement Types'!$A$2:$A$10000,0)),ROW('All Engagement Types'!$B$2:$B$10000)-ROW('All Engagement Types'!$B$2)+1)>=3,1))" but it is more than 255 characters and will not work in VBA.
Any and all suggestions would be greatly appreciated.
<tbody>
</tbody><colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup>
I've been working on a section of code for several days, am not finding any previous posts that are applicable to my situation and am desperately hoping someone is willing to undertake the challenge. I tried posting this problem last week but the responses I received were not applicable so I am trying again. I am not sure if this breaks any rules and I apologize if it does...I'm just, well, like I said, desperate.
I have ID numbers in col A, Dates in col B. The Start Date in cell C1 (1/1/2017)and the end date in Cell D1 (3/31/2017) - the end date is actually dynamic but I know how to address that.
I need to count the number of unique values in column A if the dates in column B are within the range. Here comes the tricky part...I only need to count them if there are 3 or more occurrences. For example: in this data, there is only 1 ID that appears within the date range of 1/1/17 to 3/31/17 three or more times. Although 14 and 503 both appear on the list at least 3 times, the occurrences are not between the date range. ID 414 is the only one with 3 or more occurrences within the stated date range.
My original spreadsheet used the array formula:"=SUM(IF(FREQUENCY(IF(('All Engagement Types'!$B$2:$B$10000>='Weekly BP & Engagement'!$C$1)*('All Engagement Types'!$B$2:$B$10000<='Weekly BP & Engagement'!$A15),MATCH('All Engagement Types'!$A$2:$A$10000,'All Engagement Types'!$A$2:$A$10000,0)),ROW('All Engagement Types'!$B$2:$B$10000)-ROW('All Engagement Types'!$B$2)+1)>=3,1))" but it is more than 255 characters and will not work in VBA.
Any and all suggestions would be greatly appreciated.
COL A ID | COL B Due Date |
14 | 03/07/2017 |
14 | 01/31/2017 |
14 | 06/17/2016 |
414 | 03/16/2017 |
414 | 02/15/2017 |
414 | 01/31/2017 |
503 | 03/14/2017 |
503 | 02/14/2017 |
503 | 12/12/2016 |
503 | 10/10/2016 |
503 | 09/16/2016 |
503 | 09/15/2016 |
503 | 08/29/2016 |
503 | 07/14/2016 |
503 | 06/29/2016 |
503 | 06/16/2016 |
503 | 06/09/2016 |
503 | 05/23/2016 |
<tbody>
</tbody><colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup>