I would like help in trying to come up with a way (Excel VBA?) that will count the number of calls received each day. The report has all of the data in Column A but has months of data separated by text as shown below.
I'm wondering if I could create a range between each "Call Date:xx/xx/xxxx" and count the rows that contain numbers but only for each date.
So I'd have to define the range from "Call Date:" to "Location:" -minus 1 row.
perhaps something like
But I can't figure out how to do it for the entire report which could have 10,000 rows and months of days...
I'm trying to get it so that in column B it will output:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: inherit; white-space: inherit;">
CallDate:01/18/20191call
CallDate:01/19/20198call
CallDate:01/20/20199call
</code>This is how my data looks (again it's an export of an old system so everything gets put into column A).
As there could be hundreds of pages, I'm wondering how best to go about this so that it can count the rows that contain ":" between each "Call Date:" and "Location:" with the count displayed beside each Call Date:
Thanks everyone!
I'm wondering if I could create a range between each "Call Date:xx/xx/xxxx" and count the rows that contain numbers but only for each date.
So I'd have to define the range from "Call Date:" to "Location:" -minus 1 row.
perhaps something like
Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: inherit; white-space: inherit;">[COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A:A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Find[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Call Date:"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A:A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Find[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Location"[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A:A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Find[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Call Date:"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit](-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit])).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select
[/FONT][/COLOR]</code>
But I can't figure out how to do it for the entire report which could have 10,000 rows and months of days...
I'm trying to get it so that in column B it will output:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: inherit; white-space: inherit;">
CallDate:01/18/20191call
CallDate:01/19/20198call
CallDate:01/20/20199call
</code>This is how my data looks (again it's an export of an old system so everything gets put into column A).
As there could be hundreds of pages, I'm wondering how best to go about this so that it can count the rows that contain ":" between each "Call Date:" and "Location:" with the count displayed beside each Call Date:
Thanks everyone!