Hi I have a project with about 240,000 rows of data and 23 columns. There are two columns that I need to compare dates to that are; Date Logged and Date Resolved. The date I compare them to are usually the first of each month from 2017 to present day. I need to track whether or not that case was solved by the start of that month by checking to see if it was even logged by that date and if it was resolved yet. I found that doing a countif in excel for the table was taking about 14 seconds to do the whole sheet which was not so bad so I attempted to do it in VBA to speed up the process. After writing a few lines using For and if statements I'm finding it to be very slow and takes about 14 seconds just for one column of data. What approach should I take to tackle this issue.
I should add that I load that huge table into an array and loop the For loop through it Counting all the ones that meet the requirements for the table below. There are more columns too but this is the gist of it.
[TABLE="class: cms_table, width: 305"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 526"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Backlog[/TD]
[TD]Volume Receive that month[/TD]
[TD]Completed during the month[/TD]
[/TR]
[TR]
[TD]3/31/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2019 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2019 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]3/1/2019 0:00[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I should add that I load that huge table into an array and loop the For loop through it Counting all the ones that meet the requirements for the table below. There are more columns too but this is the gist of it.
[TABLE="class: cms_table, width: 305"]
<tbody>[TR]
[TD][TABLE="class: cms_table, width: 526"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Backlog[/TD]
[TD]Volume Receive that month[/TD]
[TD]Completed during the month[/TD]
[/TR]
[TR]
[TD]3/31/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/1/2017 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/1/2018 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2019 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2019 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]3/1/2019 0:00[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Last edited by a moderator: