JimboP34

asked on

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,

I enclose an example spreadsheet.

Thanks in advance.

Jim

combinations-that-add-up-to-targ.xlsx

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,

I enclose an example spreadsheet.

Thanks in advance.

Jim

combinations-that-add-up-to-targ.xlsx

Last Comment

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,

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°)

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

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°)

ASKER

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

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

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

ASKER

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