Select and sum data using the scripting dictionary

Newton Excel Bach, not (just) an Excel Blog

Another User Defined Function (UDF) prompted by a query at Eng-Tips.

The question concerned a set of data consisting of 13 columns, which was to be divided into two sets, based on the contents of Column 4, then the contents of columns 6 to 11 were to be summed for each row with identical contents in columns 12 and 13.

The first attempt copied the data into two arrays (based on the contents of Column 4), then did a nested loop, for each row of Array1 looping through all of Array2, and adding the contents of Array2 to the current row of Array1, when columns 12 and 13 were identical in each array.  This worked, but was extremely slow, taking about 4 minutes for 50,000 rows of data.

One way to speed up the process would be to sort both arrays based on the contents of columns 12 and 13, then…

View original post 343 more words