Forum Discussion

Kreggar's avatar
Kreggar
Occasional Reader
Sep 18, 2025

Finding Possible Matches to a Solution

Hello Everyone,

I'm wondering if anyone has any tricks or formulas that I can use to find any possible combination of numbers that will create the desired solutions I'm looking for. Take a look at the picture I uploaded for reference. If I have a list of data in column A, I will be looking for a given set of numbers that will create a sum that matches column C. I'm looking for a trick or formula that will return all possible combination of numbers that will sum up to equal Column C and then return those possible combinations in another column for me to see (for example like what you see in columns E, F, & G). Does anyone have any ideas on how or if this can be done? I would like to do this without the use of complicated macros or other codes if possible.

 

excel
Formulas and Functions
office 365

2 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Sep 19, 2025

    //select rowid,* from Sheet1;

    with recursive D as (

    select f01 E,rowid old_rowid,1 level,f01 和,rowid x from Sheet1 union all select E||'+'||f01,Sheet1.rowid,D.level+1,f01+和,x||','||rowid from Sheet1,D where rowid>old_rowid and round(f01+和,2)<=40)

    select row_number() over () no,* from D where round(和,2)=40 limit 500;

     

  • mathetes's avatar
    mathetes
    Silver Contributor
    Sep 19, 2025

    (a) I'm sure it's possible. In fact, given the experience of frequently seeing multiple solutions to the posed question of "How to get from A to B?" I suspect there are at least two or three ways to get there.

    (b) however, I'd like to ask you to give a little more clarity, and maybe expansiveness, to your question before attempting to provide what probably would be one of the more simplistic solutions. The following questions are ones that occur to me, but think of them as only priming the pump.

    • Your example uses only factors of 5 in the list of "raw materials" Is that always going to be the case, or in the real work--presuming this is actually just a model of something more challenging you're actually seeking to solve--might the raw materials, the building blocks, be drawn from more random sets of numbers?
    • Or from prime numbers only? Or some other defined set?
    • Can a number be used more than once? Would a legitimate answer to your example be 20 20 or 5 5 5 5 5 5 5 5 or 10 20 10 etc.?

    I'm asking these questions in part to make the point that one of the most important tasks in designing a good spreadsheet is developing a clear and comprehensive (within reason) definition of the purpose and constraints before starting to select functions and write formulas. You've defined a purpose, but left constraints to the imagination.

Resources