# Find the number(s) that add up to a target number from a list

Dear all

I have the following problem. I have a list of numbers in column A. I want to find those numbers that add up to a given target number.

If these combinations are found then I want the adjacent cells in column B to be highlighted yellow.

Importantly I want to know if there is more than one combination of numbers that make up the target number,

Jim
Microsoft Excel

Last Comment
harfang
krishnakrkc

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
JimboP34

That is a great bit of code! Who wrote it?

Is it possible to modify so that it highlights the adjacent the matching values in the adacent columns? each set in a different colour?
harfang

JimboP34,

Maybe you don't realise the scope of the problem. Every value can be included in a given total, or not. This means you need to explore potentially 2^n cases. In the link provided by krishnakrkc, the challenge was limited to 54 values, or 2^54 possibilities meaning ~1.8E16. This is already quite a large number:  18 014 398 509 481 984. Clever algorithms can make the number of combinations to test significantly smaller, but it's still a computer intensive process. The page mentions “15 minutes of searching and it was far from done” (note the emphasis).

Now you seek to do the same for 350 values. This means 2^350combinations or ~2.3E105, a number significantly larger than the number of particles in the Universe. This is a HUGE number: 2 293 498 615 990 071 511 610 820 895 302 086 940 796 564 989 168 281 123 737 588 839 386 922 876 088 484 808 070 018 553 110 125 686 554 624

If you find an algorithms to solve this problem in a reasonable amount of time, you will be a rich man.

(°v°)
JimboP34

Harfang

Thanks for your message. Could you explain the maths behind why it is 2^n possibilities?

In addition, sometimes when I run the the code in the link excel says it is not responding. Why is this happening?

Thanks

Jimbo
SOLUTION
harfang

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY