Find cells that give max sum of array using 1 cell from each column with no duplicate rows.

mdeisenh

New Member
Joined
Aug 26, 2013
Messages
7
I'm looking for VBA code that will find the maximum sum of a range (array) of cells with the following conditions:
1) Must have exactly 1 number picked in each column
2) Must not use any row more than once, but several rows will not get used.
3) If the number is 0, you can't use it. This "0" can be changed to a number like "-1000", so if that number is used in the sum, there is no way it would give a max sum since the largest number used in any cell is 31 and the max sum would be 31x17=527.

Im trying it out for a 5x8 array (5 columns, 8 rows). Once it is working, the final array would be 17x32 (17 columns, 32 rows) - hopefully it won't take the VBA code forever to go through that large of an array. Actually, I might limit the rows to like 19 instead of 32, as rows 20-32 probably wouldn't be used to get a max sum anyways.

My temporary array is (with the 26 in cell D3):
26
18
15
0
31
0
0
0
0
0
0
0
13
0
0
0
11
0
0
0
0
0
19
0
11
0
0
0
0
0
0
20
0
0
15
25
0
0
12
0

<TBODY>
</TBODY>

The max sum of this array is 26+11+19+12+15=83
but there are other valid combinations:
26+20+13+12+11=82
26+11+13+12+15=77
26+11+13+12+11=73

I'm looking for doing both of the following:
1) Highlight the cells used for the max sum
2) On a new sheet (or could just be an unused portion at the bottom of array on present sheet), list the row used for each column, i.e., 1 4 5 8 7 for the above array as this will help me distinguish between duplicate numbers in a column.
3) Display the sum - I can do this part with sum fn.

Also, how long do you think it will take to go through a 17x32 (or possibly 17x19) array? I tried something similar a year ago and I had to put in delay loops because it would lock up :oops: running the macro, but I have a better desktop now so it should run better.

Thank You for all help...
Marty
 
Last edited:
Yup that would be one easy way of quickening things up. but being a nerd I am, I wanted to create an algorithm that could solve this without testing each combination!
Oh well, I'll just code the semi-brute force way.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top