Member Avatar for jackiejoe

OK, this might be a bit too difficult to explain on the forum but I thought I would give it a shot.

I have an excel sheet which has 3 columns,
The first and second columns contain different figures and the third column has a figure which could be a negative number (this third column is summed together to give a total)

What I have at the moment is a macro where I can enter a range for column A and a range for Column B and it hides all the rows that don't match the two ranges I put in.
E.G.
Range 1 (50-100)
Range 2 (1-12)
The macro I have will hide all the rows which have a figure outside 50-100 in the Column A and hide all the rows which have a figure outside 1-12 in Column B - both ranges must be met in order for the row not to be hidden.

Now I am left with a bunch of rows and from this I sum up Column C and get the figure I was looking for.

But what I want is a macro to work out the highest possible sum for Column C and then work out which 2 ranges are needed to get this figure, sort of working backwards to what I am doing now.

I know this sounds pretty difficult to understand but hopefully I explained it ok, I can send on the workbook if needed. Any help greatly appreciated.

Thanks,

  • 2 Contributors
  • 2 Replies
  • 132 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jackiejoe

Dani AI

Generated

The task reduces to finding the best axis-aligned rectangle in a 2‑D value space (one numeric column for each axis) that maximizes the sum of the third column inside that rectangle. The spreadsheet that attached makes this concrete; offered to look at the file, which is useful if you want an exact VBA implementation for those sheets. Below are practical, reliable approaches and implementation notes you can apply immediately.

Algorithm (efficient, exact)

  1. Replace continuous ranges by the sorted unique values in each filter column (these are the only candidate endpoints that change which rows are included).
  2. Build a p-by-q matrix M where M[i,k] = total of the value column for rows with filter1 = unique1[i] and filter2 = unique2[k].
  3. Precompute cumulative sums down the first axis so any contiguous block of filter1 rows can be produced as differences in O(q).
  4. For every start_i..end_j on axis1 (O(p^2) pairs) form a 1‑D array A[k] = sum over that filter1 block for each filter2 value, then run a 1‑D maximum contiguous-sum algorithm (Kadane) on A to get the best contiguous filter2 interval. Track the overall maximum.
    This yields an exact optimum in O(p^2 * q) time and is memory-light if p and q are the counts of unique values.

Implementation tips for Excel/VBA

  • Read ranges into VBA arrays and use a Dictionary to get sorted unique lists; avoid cell-by-cell operations for speed.
  • Use zero-based Variant arrays and Long counters. Fill M using index maps from the Dictionaries.
  • Precompute cumulative sums along the chosen axis so each F-interval is computed by subtraction (fast).
  • Handle decimals by treating each distinct value as a candidate endpoint; if the dataset is huge, bin values (percentiles) to reduce p/q.
  • Negative values are fine; Kadane handles all-negative arrays by returning the maximum single element (choose inclusion rules accordingly).
  • Output the chosen endpoints and optionally highlight the matching rows so the result can be validated against the workbook total.

If p or q exceed a few hundred and performance becomes a problem, swap axes (choose the smaller axis for the O(p^2) loops), or use 2‑D prefix sums or sampling/binning for an approximate but fast result.

Recommended Answers

All 2 Replies

Member Avatar for manoshailu

Hi,

I got some idea from your thread but if you given me that excel file then that could be helpful to me.

Have a nice day
Shailaja :)

OK, this might be a bit too difficult to explain on the forum but I thought I would give it a shot.

I have an excel sheet which has 3 columns,
The first and second columns contain different figures and the third column has a figure which could be a negative number (this third column is summed together to give a total)

What I have at the moment is a macro where I can enter a range for column A and a range for Column B and it hides all the rows that don't match the two ranges I put in.
E.G.
Range 1 (50-100)
Range 2 (1-12)
The macro I have will hide all the rows which have a figure outside 50-100 in the Column A and hide all the rows which have a figure outside 1-12 in Column B - both ranges must be met in order for the row not to be hidden.

Now I am left with a bunch of rows and from this I sum up Column C and get the figure I was looking for.

But what I want is a macro to work out the highest possible sum for Column C and then work out which 2 ranges are needed to get this figure, sort of working backwards to what I am doing now.

I know this sounds pretty difficult to understand but hopefully I explained it ok, I can send on the workbook if needed. Any help greatly appreciated.

Thanks,

Member Avatar for jackiejoe

Hi, thanks for the help I have attached the spreadsheet - basically, there is a macro Ctrl-Shift-M to run it which takes two ranges for Column F and G and it hides all the rows which dont correspond to the inputted ranges, then the Total in Cell M1 adds up all the visible figures in Column I, what I want is something that will work out two ranges which will return the largest Total. Thanks for looking...

stats.zip (50.9 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.