Hi

I have a medium/large data set in Excel that I'm looking to use VBA to output JSON files from. The files will have various cuts of the data to throw into a web portal. While I have full control over the data it is currently used to output Excel and PDf reports so any changes will have to work with these as well.

The data is structured on separate sheets and each sheet has a table structure (not an actual Excel table object) of the following style:
  • First column is a group description. There are ~15 distinct groups.
  • Second column is a date/time
  • Subsequent columns are the data with some helper columns (one has pure date, another time, some calculations from the other columns)

I want to build a JSON object for each data column, containing an entry for each time period (44 intervals), and each group or set of groups.
So if the data looks like:
Group1 19/09/2014 08:00 1
Group1 19/09/2014 08:15 3
Group1 19/09/2014 08:30 7
Group1 19/09/2014 08:45 9
Group2 19/09/2014 08:00 2
Group2 19/09/2014 08:15 4
Group2 19/09/2014 08:30 6
Group2 19/09/2014 08:45 8

I want to be able to pull the 08:00 value for group1, or the 08:00 values for some/all groups.

I have formulas that do this for me all over the sheet and drive the groups required from tables.
I would like to continue to drive them from this but if I have to hard code it then so be it.

My question is:
What is the most efficient method to extract this kind of data in VBA?

I've played with replicating my formulas but am having some issues there.
I'm assuming I'm going to have to do some for looping but want to minimise it.
I've considered applying filters to the data and summing the result but worry about the speed of processing this.
I'm wondering if building collections/arrays would be the way to go (less experience in using them)
I expect there's some arcane awesomeness I'm not aware of or am forgetting that could be used.

Anyone able to suggest an approach that will deliver results with the least processing time (as it's gotta get through a lot of values on a regular basis)?

TIA