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

ABHISKV4 · Sep 15, 2020

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

1

2

Target Number

Answer

CTN1

CTN2

CTN3

CTN4

CTN5

CTN6

CTN7

CTN8

CTN9

CTN10

CTN11

CTN12

CTN13

CTN14

CTN15

CTN16

CTN17

CTN18

CTN19

CTN20

CTN21

CTN22

CTN23

CTN24

CTN25

CTN26

CTN27

CTN28

CTN29

CTN30

CTN31

CTN32

CTN33

CTN34

CTN35

CTN36

CTN37

CTN38

CTN39

CTN40

CTN41

CTN42

CTN43

CTN44

CTN45

CTN46

CTN47

CTN48

CTN49

CTN50

CTN51

CTN52

CTN53

CTN54

CTN55

CTN56

CTN57

CTN58

CTN59

CTN60

CTN61

CTN62

CTN63

CTN64

CTN65

CTN66

CTN67

CTN68

CTN69

CTN70

CTN71

CTN72

CTN73

CTN74

CTN75

CTN76

CTN77

CTN78

CTN79

CTN80

CTN81

CTN82

CTN83

CTN84

CTN85

CTN86

CTN87

CTN88

CTN89

CTN90

CTN91

CTN92

CTN93

CTN94

CTN95

CTN96

CTN97

CTN98

CTN99

CTN100

3

36

1

4

16

3

2

8

1

7

16

3

1

5

1

16

8

1

2

1

12

1

16

11

4

11

1

16

17

1

7

1

2

1

16

1

2

1

3

1

2

1

16

1

15

14

1

4

15

1

4

2

1

5

2

8

2

8

10

16

9

8

6

11

10

17

1

2

1

3

1

8

12

1

5

1

17

1

2

3

1

11

1

10

4

24

1

2

11

1

2

1

7

2

7

10

2

10

1

2

1

2

5

1

9

1

2

1

5

8

1

8

1

2

1

2

5

1

8

1

2

9

2

8

1

10

1

2

1

2

1

3

2

1

2

1

3

2

1

5

1

10

2

1

5

18

1

5

3

1

5

2

1

7

1

4

2

1

2

1

2

5

1

2

1

5

1

2

1

2

1

7

2

1

2

1

7

1

2

1

2

1

2

1

2

7

2

8

2

1

2

1

7

6

1

2

1

2

1

2

1

2

1

6

15

2

1

2

1

2

4

1

6

1

3

5

2

1

3

1

4

1

6

1

2

4

1

2

1

2

4

1

2

1

2

1

6

2

1

2

1

3

1

2

5

1

6

1

2

1

2

7

1

2

1

2

1

7

2

1

2

1

7

12

1

3

2

1

8

10

1

2

1

4

1

2

1

2

1

2

1

4

1

2

4

1

3

2

1

2

1

6

1

2

1

2

1

2

3

2

1

2

1

2

1

2

1

2

1

3

1

2

1

2

1

2

1

2

1

4

1

9

8

1

2

1

3

1

2

1

2

1

3

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

3

1

2

1

2

1

3

1

2

1

3

2

1

10

11

Example1: Illustration of expected answer is provided below

12

36

1

4

16

3

2

8

1

7

16

3

1

5

1

16

8

1

2

1

12

1

16

11

4

11

1

16

17

1

7

1

2

1

16

1

2

1

3

1

2

1

16

1

15

14

1

4

15

1

4

2

1

5

2

8

2

8

10

16

9

8

6

11

10

17

1

2

1

3

1

8

12

1

5

1

17

1

2

3

1

11

1

10

13

14

Example2: Illustration of expected answer is provided below

15

12

1

3

2

1

Sheet2

Cell Formulas
Range	Formula
B12	B12	=D12+E12+K12
B15	B15	=C15+D15+E15+F15+G15+H15+I15+K15+L15

earthworm · Sep 15, 2020

I think this is not possible because you are asking for combination that adds up to target number . If anyone will post solution this would take time to derive because this will eat away system memory and might slow down excel . The core problem is finding the combination that matches with target. Excel would try to add each number present in column combination separately to identify the target unless the actual target is met . Its will be long loop.

example
1+4+16
4+1+16
16+1+4
1+16+4
4+16+1 and this will continue forever.

ABHISKV4 · Sep 15, 2020

earthworm said:
Its will be long loop.

Thanks for your prompt opinion. I would like to have opinion from few other super users as well.

Will it help if I narrow the range, from current 100 cells to say, 20 cells. If yes, then I don't mind doing so, at-least I will have some solution instead of nothing.

earthworm · Sep 15, 2020

ABHISKV4 said:
Thanks for your prompt opinion. I would like to have opinion from few other super users as well.

Will it help if I narrow the range, from current 100 cells to say, 20 cells. If yes, then I don't mind doing so, at-least I will have some solution instead of nothing.

You can use Excel solver ad in try below video
Solver Adin

Eric W · Sep 23, 2020

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:

Subset sum problem - Wikipedia

en.wikipedia.org

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:

Accounts Receivable Challenge

www.mrexcel.com

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!

ABHISKV4 · Sep 24, 2020

Eric W said:
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!

Many thanks @Eric W and @earthworm for your valuable feedback, at least I know now what to look for.

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

Accounts Receivable Challenge

www.mrexcel.com

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

In the suggested VBA code (which I could not decode), if you could please help me where to place the Sum that I am looking for and where to place the list of all available invoices. Also please guide which Macro I need to run, as the module when pasted makes me available 4 macros.

Eric W · Sep 24, 2020

I explain here:

Need Combination of dollar amounts that match entered dollar amount

If I enter a dollar amount (will use $1,513.30 for this example) I need an x or other identifier to show me which amounts combined match that dollar amount. What would a formula look like and maybe even a MACO? Amount Looking For Match To This Amount $1,513.30...

www.mrexcel.com

in post 2 how to use the macro. Since that post hasn't been fixed yet to show the workbook examples, here's just the explanation:

=====================================================================================

This was the subject of a MrExcel Challenge about 17 years ago. The winner wrote a macro to solve it. Quite impressive. Here's the link:

Accounts Receivable Challenge

To try it, open a new workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. Copy the code from the box that says: "IOANNIS's winning macro", and paste it into the window that opened. Press Alt-IM again. Paste the code from the "Module 2" box into that window. Go back to your Excel sheet and paste your values into B1:B100. Put the number of values in F1, and the target amount into F2.

Press Alt-F8, and choose the Challenge macro and click Run.

F14 has the start time, F15 has the end time. H1 has the solution. Take the numbers from that cell and add up the values in those rows in B1:B100, and they add up to the value in F2. If there are multiple answers, you'll get those combinations going down the H column.

=====================================================================================

I'd advise you to start small, with say 20 rows. Otherwise you could end up with millions of solutions and the macro might break since there's no room to store all the results.

Sulprobil · Sep 24, 2020

Hello Eric,

That code is awful, I am afraid

Michael Sch. has structured that well and allowed me to cite it:

Regards,
Sulprobil

ryan7 · Jun 30, 2021

Sulprobil said:
Hello Eric,

That code is awful, I am afraid
Michael Sch. has structured that well and allowed me to cite it:

Regards,
Sulprobil

Can't get any of these to work.
It causes my Excel to crash.

earthworm · Jul 2, 2021

Computers still cant beat Human Judgement till date

in this one.

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

ABHISKV4

New Member

Excel Facts

earthworm

Well-known Member

ABHISKV4

New Member

earthworm

Well-known Member

Eric W

MrExcel MVP

Subset sum problem - Wikipedia

Accounts Receivable Challenge

ABHISKV4

New Member

Accounts Receivable Challenge

Eric W

MrExcel MVP

Need Combination of dollar amounts that match entered dollar amount

Sulprobil

Banned user

ryan7

Board Regular

earthworm

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

Banned user

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock