asked on

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,

I enclose an example spreadsheet.

Thanks in advance.

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

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

JimboP34

ASKER

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?

Markus Fischer

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