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.