Find Highest 3x3 Sum

**daffodil11** · 02-04-2015, 07:22 PM

I have a dataset containing 3264 rows and 7640 columns, and I need to find the sums of each 3x3 grid, and eventually determine which are the top 10, by row & column header of upper left cell.

I've attached a scaled down version with values from B2:P29. What would an efficient approach to this be?

It's being used in conjunction with Latitude and Longitude, where each cell represents .01 degrees; actual headers are Lat and Long.
Normally I'd just use a visual heatmap but these people want data with granular detail.

To clarify, I'm mostly just looking for the list of all 3x3 grids and respective totals.

**stnkynts** · 02-04-2015, 07:58 PM

I don't know how efficient this is but I think it would work. Using 2 loops 1 for row, 1 for columns, stepping every 3 and resizeing that range you can get your 3x3 grid. You then get the summation and input the base range (before the resize) along with the summed value into 2 arrays (the 2 parameters will end up having the same position in their respective arrays, for later use). After the ranges you loop through the array to determine what the top 10 sums which can, at the same time, give you their respective ranges.

I didn't supply code because I wasn't sure if this way would be all that efficient. Figured I would offer you up a suggestion. If you want code let me know.

**daffodil11** · 02-04-2015, 08:04 PM

I was making this much harder than it was. Thank you stnkynts.

I will give the code a shot. I'm not terrible, but I haven't been coding long enough to figure out whats needed when I have nothing.

2 for loops, incrementing by range of headers and rows, output maybe = wsfunction.sum(range(cells(r,c),cells(r+2,c+2)). Give me a little time to work through the logic.

**daffodil11** · 02-04-2015, 08:38 PM

A whole lot of crashed workbooks later, I went with stnkynuts directions and built this, ultimately using DoEvents every 10 columns to prevent too much freeziness.

Please Login or Register  to view this content.

Also, I'm proud of myself and gave myself a gold star.

**MarvinP** · 02-04-2015, 08:49 PM

Hi,

Why not use relative addressing for all those sums and then do a large on the array of sums? See the attached:

**daffodil11** · 02-04-2015, 09:33 PM

I tried, but the workbook got a little to unstable at 24,936,960 data points. And most importantly, I needed to be able to refer to the Row & Column Headers of each.

Also, VB is AWESOME. I'm trying to learn it through immersion.

**MarvinP** · 02-04-2015, 10:15 PM

Hi,

Now that I see you wanted to practice your VBA, see this solution.
I throw away any sum that isn't in the top 10 by doing the sort routine.

I don't think this should take that long to do your big file.