minimum no. of cells in a row that add up exactly to a target number

ABHISKV4

New Member
Joined
May 26, 2009
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Need to highlight the minimum no. of cells in the range starting from Column C till CX for each row, such that these add up to exactly the target number. Also would like to have the list of these numbers in descending order and the cell numbers of these numbers. In case same number is available for more no. of times than needed, the leftmost cells get preference and can be used for highlighting the cell. To summarize the requirements, my expectations are as below (See Example1 of pasted excel screenshot)
1. Highlight the cells which add up to these number
2. The list of these numbers in descending order. Answer: 16,16,4
3. The cell numbers of these numbers in descending order, if a number is repeated like in case above (16), then the leftmost cell number comes first, followed by next. Answer: E12, K12 and D12

Hope i have clearly explained everything, kindly feel free to ask if any more information/clarity is required further. Both excel formulas (normal/array) and VBA code are equally welcome as a solution to this problem, thanks in advance.

Book30
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCX
1
2Target NumberAnswerCTN1CTN2CTN3CTN4CTN5CTN6CTN7CTN8CTN9CTN10CTN11CTN12CTN13CTN14CTN15CTN16CTN17CTN18CTN19CTN20CTN21CTN22CTN23CTN24CTN25CTN26CTN27CTN28CTN29CTN30CTN31CTN32CTN33CTN34CTN35CTN36CTN37CTN38CTN39CTN40CTN41CTN42CTN43CTN44CTN45CTN46CTN47CTN48CTN49CTN50CTN51CTN52CTN53CTN54CTN55CTN56CTN57CTN58CTN59CTN60CTN61CTN62CTN63CTN64CTN65CTN66CTN67CTN68CTN69CTN70CTN71CTN72CTN73CTN74CTN75CTN76CTN77CTN78CTN79CTN80CTN81CTN82CTN83CTN84CTN85CTN86CTN87CTN88CTN89CTN90CTN91CTN92CTN93CTN94CTN95CTN96CTN97CTN98CTN99CTN100
336141632817163151116812112116114111161711712116121111113121161115141114151142115282810169861110171213111181215111171231111110
424121111111111111121117271021012222221112511111119112158111811122125181112922811011121121321121113215111021
5181531111521111711421212512151112221111221117221122117121212112722821121761111112111211112211111211111
6152111121112411111611352131416112412211241211211111111111162112213125111161111211271211211721111112111
7121111111132111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
8101121411211121112114111124113222211121111612111111211111112232121111112121121312111111212221211141111
981211113312121131211222122122211111121111212212122111122211122222112211331121121113111112111111113211
10
11Example1: Illustration of expected answer is provided below
123636141632817163151116812112116114111161711712116121111113121161115141114151142115282810169861110171213111181215111171231111110
13
14Example2: Illustration of expected answer is provided below
1512121111111132111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
Sheet2
Cell Formulas
RangeFormula
B12B12=D12+E12+K12
B15B15=C15+D15+E15+F15+G15+H15+I15+K15+L15
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You insert the code into your own spreadsheet. Press ALT + F11, insert a new module, copy the code in there, go back to you spreadsheet tab, and follow Tushar's instructions.
 
Upvote 0
This is an interesting problem. So much so that I've taken a stab at it several times. But I don't try solving it anymore for a few reasons. There are 2 approaches to this. First, you can exhaustively try every combination and see if the sum is correct. But like earthworm said, the number of combinations grows rapidly. For 20 numbers, the number of combinations to check is over a million. Possible for a reasonably fast computer. For 100 numbers? Picture a 1 followed by 30 zeros. Not possible in the lifetime of the universe. The other option is to come up with a clever algorithm. Group the numbers into smaller sets maybe, eliminate duplicates, save partial sums, etc. Even so, this is NOT an easy problem! Look here:


Another reason I don't work on it anymore is because there are too many answers. Just looking at your example 1, which you solved with 16,16,4, I can see many other possibilities, such as 11,11,14. I have no doubt that if I were to write a program to find the answers, there would be millions of combinations that work, and probably thousands with only 3 numbers. How would you choose which one is the "best" answer?

Even with all that, some people have found workable solutions. See here:


That macro does the same thing you're asking. I have tried it, and it works, but I don't understand the algorithm.

So to sum up, creating such an algorithm is not an easy task, and even if it could be devised it would probably not give you the results you want. Which is why I don't work on these kinds of problems anymore. Nevertheless, I wish you luck!
Eric, can you share excel files which you have used for Account receivable challenge. It may help me for my prblem
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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