now browsing by tag
There are many scenarios where we need to categorize and split raw data.
In this scenario, we will split raw data into new workbooks, by category, using a Dictionary of Arrays.
Normally, it’s best to combine data into a single location, as I did in this article.
The main challenge is to find an efficient way to read data into categories:
- the table might not be sorted by category:
- reading and writing data row by row is very slow.
Download the demo file to try the solution:
Which is the most efficient way to collect and write data?
Based on my experience, even reading data row by row from a list object is a slow operation, the fastest way to read data is to load it into an array.
A bi-dimensional array is the best option to paste data into a worksheet, fits perfectly into a range of cells that has the same dimensions: rows and columns.
Because we want to separate data by category, we have to create an array of data for each category.
We will store the arrays into a dictionary object in this exercise, using category as a dictionary key.Read More