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

ryan7 · Jul 2, 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

Is it possible to build in multiple solutions?

Sulprobil · Jul 2, 2021

Combination of numbers that sum or match a target value

www.tushar-mehta.com

ryan7 · Jul 2, 2021

Sulprobil said:
Combination of numbers that sum or match a target value

www.tushar-mehta.com

What template should be used for this?

Sulprobil · Jul 2, 2021

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.

ExcelUser5005 · Aug 26, 2022

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

Eric, can you share excel files which you have used for Account receivable challenge. It may help me for my prblem

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

ABHISKV4

New Member

ryan7

Board Regular

Excel Facts

Sulprobil

Banned user

Combination of numbers that sum or match a target value

ryan7

Board Regular

Combination of numbers that sum or match a target value

Sulprobil

Banned user

ExcelUser5005

New Member

Subset sum problem - Wikipedia

Accounts Receivable Challenge

Similar threads

Forum statistics

Share this page

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

New Member

Board Regular

Excel Facts

Banned user

Board Regular

Banned user

New 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