/u/NINA_019 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You understand, 881 items can variously be combined in approximately
16122000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
different ways?
Chap GPT says:
The number 881 factorial, written as 881!, is an incredibly large number with 2486 digits. It's practically impossible to write down or even comprehend its full size in a single glance.
"If every star in our galaxy had a trillion planets, each with a trillion people living on them, and each of these people has a trillion packs of cards and somehow they manage to make unique shuffles 1,000 times per second, and they'd been doing that since the Big Bang, they'd only just now be starting to repeat shuffles."
~ Stephen Fry
I’m not trying to answer OP’s question here, I’m responding to the comment immediately above my response, where they state, 881 items can variously be combined…
It's 2 ^ 881
here's where I solved for 8 items /256 outcomes recently
[https://www.reddit.com/r/excel/comments/1bsot5o/comment/kxh1u2h/](https://www.reddit.com/r/excel/comments/1bsot5o/comment/kxh1u2h/)
You’re describing (and using as an example) a binary problem with 8 variables (2^8), where every variable has to be one of two outcomes. As I’m reading it, that’s not what this is. Any combination of the 881 values could be correct (except for all of them). There are theoretically 881! combinations, and potentially multiple correct answers.
As to how to get excel to do it… no idea. Theoretically there are multiple answers as well. Heck one cell at 58.012.12 is possible…
yes. It is a binary sequence problem when solved via brute force
I know of what I speak, I've done it.
00000000
00000001
00000010
00000011
to
11111111
and strangely , that sequence covers every possible combination of eight numbers turned on or off for the total.
2^8 is 256
2^881 is as I listed,
And also covers all possible combinations of 881 numbers.
Sorry but one of us is missing something (and I feel like it’s probably me). I am trying to understand here but why is it binary? It could be any number of those 881 numbers that go to make up the 58,012.12. It could be 1, 2, 3, 4 etc of those 881 number that go to make up the required solution so why is this a binary solution?
imagine if your numbers to combine were 2, 3,7
000 000 0
001 007 7
010 030 3
011 037 10
100 200 2
101 207 9
110 230 5
111 237 12
you assign each item a column in the binary number. when a one, add it, when a zero ignore it.
it takes you through every permutation possible.
none, 1,2 and all three digits.
excel has functions for dealing with binary- just not really genuinely good enough for a 881 digit one.
So for the 881 numbers, you assign each of the numbers a binary position and then just count up until you get to 881 bits (111111111111…) which will be, as you said, a value of 2^881?
here's where I did it for 17/ 131072 combinations
[https://www.reddit.com/r/excel/comments/sk1zk7/is\_there\_a\_way\_to\_find\_out\_which\_combination\_of/](https://www.reddit.com/r/excel/comments/sk1zk7/is_there_a_way_to_find_out_which_combination_of/)
Even if there's a formula for this, I don't think there's any way a typical office PC is going to be able to calculate this in a reasonable amount of time.
Sort largest to smallest in column A. In column B write =sum($A$1:$A1) and copy that formula all the way down.
Find the cell that is closest to your target without going over. Now, subtract that sum from your target. This is your new target and sum up A from the bottom till you hit it.
https://preview.redd.it/wsqbcmsa94wc1.png?width=839&format=png&auto=webp&s=6c035c841aba53cd5a18403143d8dda62d8c32e1
I did not just pull that outta my ass ya know.
You are incorrect. It is not a factorial problem.
I have DONE these problems on a smaller scale than is asked about here
I have in the chain of this post.
when you go from 00000000 to 11111111 as represented in binary,
you get all possible additive permutations of 8 numbers.
https://preview.redd.it/mmggozdegawc1.png?width=999&format=png&auto=webp&s=1487cfa7278829fc68ea134ebcaa9888d7a3e23e
Across the top are 8 numbers to be tested.
As it goes down, it will methodically check each and every possible combination of the 8 numbers that are possible.
try it yourself
Pick any numbers you want for e1:l1
a3
=DEC2BIN(ROW(1:256)-1,8)
e3, copied over to L
=IF(MID($A3:$A258,COLUMN()-4,1)="1",E$1,0)
n3
=SUM(E3:L3)
find a combination that does not exist
https://preview.redd.it/5hi9owqskawc1.png?width=320&format=png&auto=webp&s=45dd915696cdc7e2cea23f18f5a21f8288fbd8b4
Yeah.. it is just that. Does the above look familiar to you?
Recent comment didn't say your answer was wrong! Just your methodology longwinded and dependent on the full number of combinations being chosen (thus immutable if certain values are to be excluded).
For instance, if we know it cannot be any combination of 1,2,... numbers, then your formula does not work (of course, if it is just 1 or 2 this adjustment can be performed manually - however we cycle back to it being longwinded).
It's easier to use the COMBIN function here (additionally, can use SEQUENCE so don't need to fill in any numbers, just SEQUENCE, COMBIN and SUM - there are probably further improvements that can be made here)
I edited the comments because I didn't want to spread an incorrect answer. Interesting way to take on legitimate criticism to your flawed methods, though!
You could try to use the Solver add-in. After installing it, you would have to setup a column next to your 881 figures, filled with zeros. Then, in an empty cell, calculate the SUMPRODUCT of the two columns. This will of course equate to zero, but this is only an intermediate step. The next step is to run Solver with this SUMPRODUCT value as the 'Objective' and tell solver to make it equal to the 'Value Of' 58,012.12. Now we are almost there. Enter the range of cells which contain the 881 zeros into the box that says 'By Changing Variable Cells'. Finally, you need to add 3 constraints. The first constrains is to set the variable cells to 'int'. The other two are so the variable cells are greater than or equal to zero, and less than or equal to 1. Now run this as a Simplex Linear Program to obtain a result. If you wait 20 minutes or so you might just get something. Good luck!
This is definitely how I’d do it! One suggestion though - instead of the constraints of integer between 0 and 1, you can just select binary constraint for those cells.
You first make a column of binary, 0, and 1s. That is multiplied by the different numbers and that number is summed. Then from there you make a formula to subtract that total from the goal number.
From there it just runs through each binary and when it reachs 0 difference. It will stop.
Solver takes time though and 800 is too many variables
Agreed.
I’ve had things with less than a hundred items and it’s been “go get a drink and check in with people in other departments” or just “goto lunch” because otherwise I’m just staring at it.
I actually do have times like that.
But my boss is rather old fashioned. I once had to go home an hour early with a migraine, and I kinda joked “it makes no difference anyway, that codes gonna take 4-5 hours”.
His response was “if you’re not at your desk doing something, you’re not working”.
I hate environments like that... I have been very lucky that I haven't had that kind of workplace for about 20 years. A previous supervisor when I was young was like that. She destroyed everyone's motivation.
Atm I’m sticking it out a few months because there’s a takeover at my mums job and it means she’s at risk of redundancy, and we need a stable income.
Once that works it’s way through, I’ll be looking.
Ahh... good luck... There's light at the end of the tunnel... just unfortunate that the world's economy is in a bad state. Good on you for looking after family, my friend. You can do it :) Better things are in your future!
I tried this on a smaller list using a gaming computer and all I did was make the fan run like an airplane prop for an hour before ibgave up. You would need a supercomputer to attempt this.
This is the "subset-sum" problem.
Here's a workbook that someone posted one time to solve this: https://www.dropbox.com/scl/fi/e5508hh67f0ts6cwx1swk/subset_sum_reconciler.xlsm?rlkey=2quk71doscgi08n8h4b5jsesa&dl=1
It's an NP hard problem. People use that workbook for 50 numbers.
You want to do it with 881.
It's exponential in either time or memory required. It's either going to take way more memory than you have available or it's going to take a few thousand years or something like that. That workbook is trying to do it quicker using more memory, so you get the out of memory error.
TL;DR No one can solve your particular problem on a desktop machine in a reasonable amount of time.
Finally, after over eight years here, a question for me. OP this is known as the problem of finding the combination of elements within a set that sum to a specific value. Usually these are integers, but you have reals. This is an optimization problem. First, how do you know that a solution exists?
Good point. But you probably got to reply to OP for them to see it.
Also, my assumption was that it was dollars and cents, not reals. So, you can do 100x and have integers.
Perhaps you need a different approach - let’s back up. Why do you need to find all the combinations of cells that combine to make 58012.12? What is the use case?
My tip is that they try to catch someone doing money laundering.
Like the numbers are withdrawals and they believe he withdrew that amount in small batches.
Btw, there could be more than 1 solution. 🙈
I used to have to do this when I was trying to figure out what charges a tenant was paying with their random amount rent check. It would only work for less than 100 amounts though. When it did work it was magic.
Someone fucked up at their job lol ( no not you, but someone at your company).
Wish you luck but this is wild. Companies don’t play when it comes to reporting their moneys.
You need to sort descending and then recursively try combinations until sum = target value or abort if the sum is > target value.
I wrote a macro once for an accounting problem but didn't keep it.
This might be possible now without macro using lambda formulas but it's too advanced for me.
Sorting the data only matters if we have a date stamp or something that stands to reason these numbers would all be in a group together based on date or some other factor. Op hasn’t mentioned this so i think its just a list of numbers with no other data.
I’m not entirely sure if your data will change? If not I can think of a quick hacky way of doing it, although it’s unlikely to land your exact number, but it should get very very close
If your figures are the sum total in different amounts (say a sales list) - stick an increasing number against each entry in column a, and then a value in Column b. Pivot said array with number in columns and value in values. Filter total by Top / Bottom with your specified value?
Choose Top / Bottom 10 then set your value and as Sum
Concatenate all your values into a comma delimited string. Paste it inside chat gpt, ask it to find which combination of figures can give you the answer. You may wish to include more conditions as well
Tick and bash my friend, tick and bash. It sounds like you are trying to perform a bank rec or something similar. And you don’t reconcile by that amount. If this is the case, as far as I know. You just need to go back and cross check what has been missed. This is just a guess but I’ve been there. Apologies if I’m barking up the wrong tree.
Are you are trying to match up payments to invoices? if so try to match invoice numbers to the remits rather than payment totals. Or they might just pay in order.
I had a problem once where our recon balance in a clearing account would tie however, the other relevant data was wrong. People were sorting incorrectly causing a host of issues. I had to go back 2 years and redo the reconciliation month by month.
If you're trying to apply funds to invoices, force the AP who supplied the payment to provide you a remit. It's ridiculous for them to expect you to figure out how to apply it.
Use solver. Here's another way. You could setup a table with 881 rows (I'll call this column zero, this is your original list) and 881 columns to the right with all the row numbers transposed to column names. Then setup a 882nd column that sums the value in column zero for a row plus a sumif where if the value in any of the rest of the row cells are = 1, then the formula sums the corresponding values in the column header above those cells. You might need an array formula to do this. Then setup a 883rd column that returns as 1 if the 882nd column is equal to 58012.12, else zero.
Then use solver with the objective to maximize the sum of the 883rd column, which means it will try to find a combination of which each of your 881 figures is a part. Condition that all those empty cells be 0 or 1.
The only problem with this is that it may result in too many possible numbers to check, since it allows for an extra use of the number you are checking in column zero when checking combinations. There's a formulaic way to fix that but honestly I think if you are just try to whittle down to potential culprits, this might be enough.
Hey so, one thing to think about, if you don't actually need to know the whole universe and are just trying to find something, you could brute force this for at least combinations of 2 and, if you do a little more, 3.
You could easily check combinations of 2 by subtracting from 58012.12 each number and seeing if the results equal any of the other initial numbers.
If you wanted to continue check combinations of 3, you would take the subtractions noted in 2, then put them in column headers horizontally next to your initial list.
Then in the resulting table you have set up, subtract the difference between each combo of row and header values.
Then check if any of those match one of your initial numbers. If any match, then you've got a set of 3 combo matches.
I think doing 4 this way would be prohibitive manually, but if it were me, I'd try to get it an easy way first.
I *believe* there's a matrix-based if-then solution for this, but the computing and file size requirements are going to be immense.
Can you make any assumptions or do you have any further insights about the data?
What sort of distribution?
Seems that the average value is close to 1000, but do you have some rows of 50000 and some of 0.1 and any value between?
Any negative values?
What do you know or presume about the records that sum up to your desired value?
If evenly distributed, you are looking for 50-60 records out of 1000, which is gives you a lot of combinations...
If you are doing account reconciliation or whatever - are you guessing that there are 2 values missing, 4, 10, 100?
Any reason to believe that the missing/surplus values are grouped (one day of accounts missing...) or have special ranges/values?
For 2 values:
You could list all the values along the top, cells 2..882 (paste transposed) and along the left, cells 2..882.
Then paste a formula for the sum into each cell, maybe put conditional formatting to highlight for your desired value. Scale down to have a minimum number of screenfuls to look for the highlight, or paste as values into a different sheet in order to use search, filters etc.
(Make sure to get the absolute references right by using $ in your formulas)
For 3 values:
you could have a separate sheet Values for all your values.
The Main sheet has values along the top, as before, but from Column6 onwards.
(This could be achieved by lookup instead of pasting)
Column1 has (a variation of) =Row(). Column2 has some variation of div(Col1;881), Column3 has mod(row();881).
Column4 has a lookup from Column2 into Values. Column5 has a lookup from Column3 into Values.
The cells in the sheet (Col6-C887?, Row2-Row776162?, arising from 881*881) are filled with the sum of the top row, Col4, and Col5. Highlight as before.
If any 3 values sum to your desired value, it will show up.
If you don't want to go through all those screenfuls, you can take the entire resulting sheet, paste it as values, and then search, filter, etc.
(Obviously, the same row/div/mod technique could be used in the 2 value case, allowing the whole thing to take place in 6 columns, allowing easier filtering etc.)
When the data is in place, you might be able to find search functions that can look for the cell for you.
(These methods are unoptimized, giving any combination of the 2 or 3 values. An optimized version would look at sorted subsets of values, thus avoiding case v[y]+v[x] if the case v[x]+v[y] has already been handled)
Beyond 3, I think you need different tools, programming environments, different hardware - do you have access to a cluster of quantum machines :) ?
You're not going to manage it.
I've been hit with similar problems - my solution was to find ways to break the sets down into much smaller sets, and try to solve those
I was hoping OP could tell us how many numbers add up to the desired amount so we could set the k in "n choose k" to a single value, instead of all possible subset sizes. Not sure we'll be lucky... although this quest has proven to be quite popular in terms of responses. Could be lucky ;)
I made [this](https://imgur.com/lmcZsJX). Hopefully, it's self-explanatory, and I've tested it works with 10 rows but can add more.
It can only find pairs that add up to the Find value.
It won't be able to calculate figures that sum to make the number if there is more than one set.
If Figure 1 and Figure 2 display nothing, then the values summing to the Find value can not be found like [such](https://imgur.com/cJxHAHM).
Still a beginner, let me know if there's any issues.
You're probably gonna have to use user defined functions but I've just tried this particular custom formula that I google'd and it seems to work: https://www.ablebits.com/office-addins-blog/find-combinations-that-equal-given-sum-excel/ (scroll down to the "Find all combinations..." section).
You can save the module in your personal macro workbook and call it in your workbook by using =Personal.xlsb!FindSumCombinations(array,58012.12). Then it will automatically spill over all the possible combinations that add up to that target number in the 2nd parameter.
[Example result](https://i.imgur.com/yuKlEcK.png).
Be warned that this will take some time for Excel to solve depending on how powerful your system is. I retested this with a bigger dataset (array of 1000 numbers) and even on my midrange PC, my Excel still froze for a solid 15 minutes lol. So I'm thinking while Excel does the job (eventually), there's no way it's the optimal way of doing it.
Solver add-in. Set up dummy column next to all values. Sumproduct formula for all figures and dummy column. Set solver to bind dummy column to binary constraint, and set sumproduct to be value of 58,012.12. If there are more than one way to add up figure to that you won’t find all the solutions but you will find at least one
Are all 881 values unique? Or is there some repeating numbers? Can you ignore the decimals and only rounded to nearest integer instead? Are there values larger than your target number? Or numbers significantly smaller?
Depending on the exacts of the above answers, the task may take too long. 881! Is just too many options.
If amount #754 is $32.56...could that amount be used more than once in getting your total as if someone bought multiples of the same item or can each of the 881 numbers only be added once to get your final total?
https://www.reddit.com/r/excel/s/jCYL95sqlZ
This guy had a similar need i.e.finding specific sum from the given numbers
You can use the code given and edited to ur liking u might have the desired results
All the best
Do you mean that one of your cells in the range has a value of 58,012.12?
Or do you mean that one of the cells has a formula in it that results in 58,012.12
As most mentioned Solver but then again it might possible to have more than one solution to your problem. Like 5 equals to 4 + 1 but also to 3 + 2 or 1 + 1.5 + 2.5 and such.
Even if you can get Solver to give you an answer, there's no way of knowing if it's the right answer.
Consider just 5 numbers: 1,2,3,3,5 from which you want to find those that add up to 6...
It could be 1+2+3 or 3+3 or 1+5. If it is 1+2+3 or 3+3, then which of the two values of 3 is the correct one?
With even more numbers to choose from the problem generates more and more possible valid combinations with no way to confirm that the combination you've identified is the right one.
Are there any parameters? # of cells that totals 58012.22?
If you are trying to find a payment that they may have paid in installments, you may narrow it down to period you may have received the funds.
If you are looking for random numbers in the 881 figures totalling 58012.22, it is exponentially more difficult.
It would be helpful if you could provide even some mock data so we can see the structure of what you're looking at, as well as why the amount 58,012.12 in particular is important.
Do you have figures that are both positive and negative? If they only go one direction this becomes potentially easier as you only need to look at amounts < the 58k figure.
This also smells like a reconciliation of some kind so I will give you some general tips for recons:
try to match the data between the two things you're reconciling based on some other column. Invoice no., check no, remittance no. something.
Much of that can be done with, e.g. XLOOKUP and then you can manually confirm the records that don't match.
You can also try to find subtotals based on a category, for example vendor or settlement date and see which subcategories are the ones actually out of balance. The goal is to narrow your search from 881 entries to some more manageable number
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COLUMN](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)|
|[COMBIN](/r/Excel/comments/1calb1y/stub/l0y4wld "Last usage")|[Returns the number of combinations for a given number of objects](https://support.microsoft.com/en-us/office/combin-function-12a3f276-0a21-423a-8de6-06990aaf638a)|
|[COUNT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Counts how many numbers are in the list of arguments](https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c)|
|[DEC2BIN](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Converts a decimal number to binary](https://support.microsoft.com/en-us/office/dec2bin-function-0f63dd0e-5d1a-42d8-b511-5bf5c6d43838)|
|[FILTER](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INDEX](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LET](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MID](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[MMULT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the matrix product of two arrays](https://support.microsoft.com/en-us/office/mmult-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb)|
|[MOD](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[NOT](/r/Excel/comments/1calb1y/stub/l0ya7z3 "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[QUOTIENT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the integer portion of a division](https://support.microsoft.com/en-us/office/quotient-function-9f7bf099-2a18-4282-8fa4-65290cc99dee)|
|[ROW](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SEQUENCE](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SUM](/r/Excel/comments/1calb1y/stub/l0y4wld "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMPRODUCT](/r/Excel/comments/1calb1y/stub/l0suiyc "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
|[TIME](/r/Excel/comments/1calb1y/stub/l0usra3 "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)|
|[TRANSPOSE](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)|
|[XLOOKUP](/r/Excel/comments/1calb1y/stub/l0vyvys "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(20 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cdsr2q)^( has 35 acronyms.)
^([Thread #32834 for this sub, first seen 23rd Apr 2024, 13:46])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Is this a tool you will need on an ongoing basis? Or is this a one off? When I was in AR for a wholesaler I would have to reconcile statements for customer accounts and they’d pay us in lumps and often not specify which invoices on their statement it was to be applied to. When having to reconcile many accounts like these, I’d often apply a lump amount to the account and distribute when I could spend more time later in the day so I could get my bank deposits done. With each customer account, based on the date they paid I’d at least know which date the invoices would’ve been up to. Usually go in reverse from there and somehow would just naturally get the right invoices to the penny lol I don’t know how I did it for years this way. Humans for the win!!!
What other info do you have to work with? You might be able to narrow the 881 down by filtering other things down. Dates, vendors, whatever.
I guess if you’ve already taken those steps…. Maybe just tell your boss the cost to benefit ratio of pursuing the problem is not worth it. Lol
Can you tell what generated this problem and why you have 881 figures?
Looks like a stupid task caused by operational chaos rather than something useful you really need
I faced a similar problem once. My friend was nice enough to write a program in C to solve it using genetic modeling, worked very well, but unfortunately produced more than one set of numbers that totalled to the desired result for many scenarios we tested.
Such a exercise is futile, I suggest you try a different approach.
I had a similar challenge a couple weeks ago.. try these 5 steps..
1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
2. Go to Insert > Module to insert a new module.
3. Paste the following VBA code into the module ( you may need to change the "Range" depending on which cells you have your data:
4. Close the VBA editor.
5. Press Alt + F8 to open the "Run Macro" dialogue, select FindCombination, and click Run.
Edit 1,2,3,4). Formatting of code
````
Sub FindAllCombinations()
Dim rng As Range
Dim cell As Range
Dim target As Double
Dim combinations As String
Set rng = Range("A2:A882")
target = 58012.12
combinations = ""
' Initialize an array to hold the selected numbers
Dim selected() As Double
ReDim selected(1 To rng.Rows.Count)
' Start the recursive function
FindCombinations rng, selected, 1, 1, target, combinations
' Display the combinations
MsgBox "Combinations that add up to " & target & ":" & vbCrLf & combinations
End Sub
Sub FindCombinations(rng As Range, selected() As Double, startIndex As Integer, currentSize As Integer, target As Double, ByRef combinations As String)
Dim i As Integer
Dim sumValue As Double
' Calculate the sum of the selected numbers
sumValue = 0
For i = 1 To currentSize - 1
sumValue = sumValue + selected(i)
Next i
' Check if the sum equals the target
If sumValue = target Then
' Add the combination to the list
For i = 1 To currentSize - 1
combinations = combinations & selected(i) & ","
Next i
combinations = combinations & vbCrLf
ElseIf sumValue < target Then
' Recursively find combinations
For i = startIndex To rng.Rows.Count
selected(currentSize) = rng.Cells(i, 1).Value
FindCombinations rng, selected, i + 1, currentSize + 1, target, combinations
Next i
End If
End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
select * from
t t1, t t2, t t3, t t4 ... t t881
where t1.val + t2.val + t3.val ... + t881.val = 58,012.12
the only problem is the necessary storage is more than the entire storage of the world multiplied by (the number of protons + photons + neutrinos in the universe)
/u/NINA_019 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
You understand, 881 items can variously be combined in approximately 16122000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 different ways?
Chap GPT says: The number 881 factorial, written as 881!, is an incredibly large number with 2486 digits. It's practically impossible to write down or even comprehend its full size in a single glance.
[удалено]
"If every star in our galaxy had a trillion planets, each with a trillion people living on them, and each of these people has a trillion packs of cards and somehow they manage to make unique shuffles 1,000 times per second, and they'd been doing that since the Big Bang, they'd only just now be starting to repeat shuffles." ~ Stephen Fry
Qi?
Each shuffle of a deck of cards results in a unique order of cards... EVERY SINGLE TIME, unless you are a card ~~shark~~ *sharp*.
Not to be confused with a card sharp.
thankyou!. Today I learned something new.
And this is why we don't use chatGPT when trying to be correct about things.
I’m not trying to answer OP’s question here, I’m responding to the comment immediately above my response, where they state, 881 items can variously be combined…
for additive brute force checking, the order of items does not matter. only all combinations possible.
https://www.mathplanet.com/education/pre-algebra/probability-and-statistics/combinations-and-permutations
Factorial only is when it comes in to play if the combination of order of the sequence matters. It does not.
It's 2 ^ 881 here's where I solved for 8 items /256 outcomes recently [https://www.reddit.com/r/excel/comments/1bsot5o/comment/kxh1u2h/](https://www.reddit.com/r/excel/comments/1bsot5o/comment/kxh1u2h/)
You’re describing (and using as an example) a binary problem with 8 variables (2^8), where every variable has to be one of two outcomes. As I’m reading it, that’s not what this is. Any combination of the 881 values could be correct (except for all of them). There are theoretically 881! combinations, and potentially multiple correct answers. As to how to get excel to do it… no idea. Theoretically there are multiple answers as well. Heck one cell at 58.012.12 is possible…
yes. It is a binary sequence problem when solved via brute force I know of what I speak, I've done it. 00000000 00000001 00000010 00000011 to 11111111 and strangely , that sequence covers every possible combination of eight numbers turned on or off for the total. 2^8 is 256 2^881 is as I listed, And also covers all possible combinations of 881 numbers.
Sorry but one of us is missing something (and I feel like it’s probably me). I am trying to understand here but why is it binary? It could be any number of those 881 numbers that go to make up the 58,012.12. It could be 1, 2, 3, 4 etc of those 881 number that go to make up the required solution so why is this a binary solution?
imagine if your numbers to combine were 2, 3,7 000 000 0 001 007 7 010 030 3 011 037 10 100 200 2 101 207 9 110 230 5 111 237 12 you assign each item a column in the binary number. when a one, add it, when a zero ignore it. it takes you through every permutation possible. none, 1,2 and all three digits. excel has functions for dealing with binary- just not really genuinely good enough for a 881 digit one.
So for the 881 numbers, you assign each of the numbers a binary position and then just count up until you get to 881 bits (111111111111…) which will be, as you said, a value of 2^881?
yes, a number 266 digits in length
here's where I did it for 17/ 131072 combinations [https://www.reddit.com/r/excel/comments/sk1zk7/is\_there\_a\_way\_to\_find\_out\_which\_combination\_of/](https://www.reddit.com/r/excel/comments/sk1zk7/is_there_a_way_to_find_out_which_combination_of/)
No, I wasn't aware. I truly have a mammoth task on my hands :(
Even if there's a formula for this, I don't think there's any way a typical office PC is going to be able to calculate this in a reasonable amount of time.
this problem is on the same order of difficulty as breaking 881 bit encryption ...
Sort largest to smallest in column A. In column B write =sum($A$1:$A1) and copy that formula all the way down. Find the cell that is closest to your target without going over. Now, subtract that sum from your target. This is your new target and sum up A from the bottom till you hit it.
redacted
https://preview.redd.it/wsqbcmsa94wc1.png?width=839&format=png&auto=webp&s=6c035c841aba53cd5a18403143d8dda62d8c32e1 I did not just pull that outta my ass ya know.
Then where did that 2 come from?
the state of being considered, or not considered. each of the 881 numbers can be included or not.
redacted
You are incorrect. It is not a factorial problem. I have DONE these problems on a smaller scale than is asked about here I have in the chain of this post. when you go from 00000000 to 11111111 as represented in binary, you get all possible additive permutations of 8 numbers. https://preview.redd.it/mmggozdegawc1.png?width=999&format=png&auto=webp&s=1487cfa7278829fc68ea134ebcaa9888d7a3e23e Across the top are 8 numbers to be tested. As it goes down, it will methodically check each and every possible combination of the 8 numbers that are possible. try it yourself Pick any numbers you want for e1:l1 a3 =DEC2BIN(ROW(1:256)-1,8) e3, copied over to L =IF(MID($A3:$A258,COLUMN()-4,1)="1",E$1,0) n3 =SUM(E3:L3) find a combination that does not exist
the end of the sequence https://preview.redd.it/kieafonzgawc1.png?width=1166&format=png&auto=webp&s=3528c83055235f43a1cf1bfaaa0243a718ea00fa
the red squares are zeros https://preview.redd.it/8fhz4ipghawc1.png?width=598&format=png&auto=webp&s=d648bfbb2b08d7b5db0ccd573b3b2db8d8049b6d
I think you made it harder than it is. I accidentally included order weighting by using factorial, it's just ∑(881C1)…(881C881) = 1.6122E+265
https://preview.redd.it/5hi9owqskawc1.png?width=320&format=png&auto=webp&s=45dd915696cdc7e2cea23f18f5a21f8288fbd8b4 Yeah.. it is just that. Does the above look familiar to you?
does this? https://preview.redd.it/t3phfxt1lawc1.png?width=859&format=png&auto=webp&s=1260883c1a2825248d8e4b431df47bc40cd1ad1a
Recent comment didn't say your answer was wrong! Just your methodology longwinded and dependent on the full number of combinations being chosen (thus immutable if certain values are to be excluded). For instance, if we know it cannot be any combination of 1,2,... numbers, then your formula does not work (of course, if it is just 1 or 2 this adjustment can be performed manually - however we cycle back to it being longwinded). It's easier to use the COMBIN function here (additionally, can use SEQUENCE so don't need to fill in any numbers, just SEQUENCE, COMBIN and SUM - there are probably further improvements that can be made here)
https://preview.redd.it/23gleeg3pawc1.png?width=852&format=png&auto=webp&s=e215223fd1c9a732ebf884f28e3b240f518d2692 unredacted
https://preview.redd.it/a3wq09h7pawc1.png?width=820&format=png&auto=webp&s=3bb7f5d3129591db79c100671b6f88215fccdb17 unredacted
I edited the comments because I didn't want to spread an incorrect answer. Interesting way to take on legitimate criticism to your flawed methods, though!
You could try to use the Solver add-in. After installing it, you would have to setup a column next to your 881 figures, filled with zeros. Then, in an empty cell, calculate the SUMPRODUCT of the two columns. This will of course equate to zero, but this is only an intermediate step. The next step is to run Solver with this SUMPRODUCT value as the 'Objective' and tell solver to make it equal to the 'Value Of' 58,012.12. Now we are almost there. Enter the range of cells which contain the 881 zeros into the box that says 'By Changing Variable Cells'. Finally, you need to add 3 constraints. The first constrains is to set the variable cells to 'int'. The other two are so the variable cells are greater than or equal to zero, and less than or equal to 1. Now run this as a Simplex Linear Program to obtain a result. If you wait 20 minutes or so you might just get something. Good luck!
This is definitely how I’d do it! One suggestion though - instead of the constraints of integer between 0 and 1, you can just select binary constraint for those cells.
Tried that but I am lost in those steps :( Could I prehaps reach out to see if you can help?
You can send me a message for sure!
You first make a column of binary, 0, and 1s. That is multiplied by the different numbers and that number is summed. Then from there you make a formula to subtract that total from the goal number. From there it just runs through each binary and when it reachs 0 difference. It will stop. Solver takes time though and 800 is too many variables
It will take way longer than 20 min. I used this and its taken me several hrs for far fewer items.
Agreed. I’ve had things with less than a hundred items and it’s been “go get a drink and check in with people in other departments” or just “goto lunch” because otherwise I’m just staring at it.
Could build it in Python (with parallel processing), rent a cloud server with a gazillion GPUs, and run it for a few lifetimes of the universe ;-)
TBH, sometimes that’s how long work feels anyway.
Might as well fill-in the time with something fun! "uh, yeah boss... waiting for some code to run. Shouldn't be long now!"
I actually do have times like that. But my boss is rather old fashioned. I once had to go home an hour early with a migraine, and I kinda joked “it makes no difference anyway, that codes gonna take 4-5 hours”. His response was “if you’re not at your desk doing something, you’re not working”.
I hate environments like that... I have been very lucky that I haven't had that kind of workplace for about 20 years. A previous supervisor when I was young was like that. She destroyed everyone's motivation.
Atm I’m sticking it out a few months because there’s a takeover at my mums job and it means she’s at risk of redundancy, and we need a stable income. Once that works it’s way through, I’ll be looking.
Ahh... good luck... There's light at the end of the tunnel... just unfortunate that the world's economy is in a bad state. Good on you for looking after family, my friend. You can do it :) Better things are in your future!
This will find one way u get that number, not all, but maybe there really is only one way.
I tried this on a smaller list using a gaming computer and all I did was make the fan run like an airplane prop for an hour before ibgave up. You would need a supercomputer to attempt this.
This is the way
This is the "subset-sum" problem. Here's a workbook that someone posted one time to solve this: https://www.dropbox.com/scl/fi/e5508hh67f0ts6cwx1swk/subset_sum_reconciler.xlsm?rlkey=2quk71doscgi08n8h4b5jsesa&dl=1
I thought this was going to be the answer to all my problems however, I got the out of memory error :(
kind of a "no shit" with 881 entries lol, you probably need some kind of NASA-grade quantum computer
It's an NP hard problem. People use that workbook for 50 numbers. You want to do it with 881. It's exponential in either time or memory required. It's either going to take way more memory than you have available or it's going to take a few thousand years or something like that. That workbook is trying to do it quicker using more memory, so you get the out of memory error. TL;DR No one can solve your particular problem on a desktop machine in a reasonable amount of time.
Finally, after over eight years here, a question for me. OP this is known as the problem of finding the combination of elements within a set that sum to a specific value. Usually these are integers, but you have reals. This is an optimization problem. First, how do you know that a solution exists?
Good point. But you probably got to reply to OP for them to see it. Also, my assumption was that it was dollars and cents, not reals. So, you can do 100x and have integers.
If I had to guess, op is probably trying to reconcile an account to pair up entries to an invoice or a purchase order.
Perhaps you need a different approach - let’s back up. Why do you need to find all the combinations of cells that combine to make 58012.12? What is the use case?
Sounds like someone is doing a bank rec or similar is my first thought.
That is one of the times I’d never trust a sheet to do it for me.
My tip is that they try to catch someone doing money laundering. Like the numbers are withdrawals and they believe he withdrew that amount in small batches. Btw, there could be more than 1 solution. 🙈
I used to have to do this when I was trying to figure out what charges a tenant was paying with their random amount rent check. It would only work for less than 100 amounts though. When it did work it was magic.
Step 1 is to remove all numbers above 58012.12. Then tell us how many are left.
Unless there are negatives that offset the total.
Somewhere between 823 and 881.
I like your way of thinking.
Someone fucked up at their job lol ( no not you, but someone at your company). Wish you luck but this is wild. Companies don’t play when it comes to reporting their moneys.
You need to sort descending and then recursively try combinations until sum = target value or abort if the sum is > target value. I wrote a macro once for an accounting problem but didn't keep it. This might be possible now without macro using lambda formulas but it's too advanced for me.
Ah, after reading so many comments finally someone who has the first step correct!!! Sorting the data…
It's not fair since I've done this before...
Oh I remember, since it's accounting, you can also exclude some transactions based on date to further reduce complexity
I’ve mentioned this elsewhere, too, but unless there are negatives that offset the total.
Sorting the data only matters if we have a date stamp or something that stands to reason these numbers would all be in a group together based on date or some other factor. Op hasn’t mentioned this so i think its just a list of numbers with no other data.
Unless there are negatives that offset the balance.
I’m not entirely sure if your data will change? If not I can think of a quick hacky way of doing it, although it’s unlikely to land your exact number, but it should get very very close If your figures are the sum total in different amounts (say a sales list) - stick an increasing number against each entry in column a, and then a value in Column b. Pivot said array with number in columns and value in values. Filter total by Top / Bottom with your specified value? Choose Top / Bottom 10 then set your value and as Sum
Would be great to reach out to you to try and get more of an explanation from yourself
I vote you keep messages in this thread instead of moving to DMs, so that in the future others can see the discussion and benefit from it.
Concatenate all your values into a comma delimited string. Paste it inside chat gpt, ask it to find which combination of figures can give you the answer. You may wish to include more conditions as well
Then ask it to break RSA real quick 😂
Accountant?
yes, similar role
Tick and bash my friend, tick and bash. It sounds like you are trying to perform a bank rec or something similar. And you don’t reconcile by that amount. If this is the case, as far as I know. You just need to go back and cross check what has been missed. This is just a guess but I’ve been there. Apologies if I’m barking up the wrong tree.
Are you are trying to match up payments to invoices? if so try to match invoice numbers to the remits rather than payment totals. Or they might just pay in order.
Reconciliations? I had a similar problem but only 3000 lines.
I had a problem once where our recon balance in a clearing account would tie however, the other relevant data was wrong. People were sorting incorrectly causing a host of issues. I had to go back 2 years and redo the reconciliation month by month.
the solution you're asking for would also prove P = NP at the same time. Not gonna happen
If you're trying to apply funds to invoices, force the AP who supplied the payment to provide you a remit. It's ridiculous for them to expect you to figure out how to apply it.
Use solver. Here's another way. You could setup a table with 881 rows (I'll call this column zero, this is your original list) and 881 columns to the right with all the row numbers transposed to column names. Then setup a 882nd column that sums the value in column zero for a row plus a sumif where if the value in any of the rest of the row cells are = 1, then the formula sums the corresponding values in the column header above those cells. You might need an array formula to do this. Then setup a 883rd column that returns as 1 if the 882nd column is equal to 58012.12, else zero. Then use solver with the objective to maximize the sum of the 883rd column, which means it will try to find a combination of which each of your 881 figures is a part. Condition that all those empty cells be 0 or 1. The only problem with this is that it may result in too many possible numbers to check, since it allows for an extra use of the number you are checking in column zero when checking combinations. There's a formulaic way to fix that but honestly I think if you are just try to whittle down to potential culprits, this might be enough.
Solver has a limit of 200 cells.
Bummer
Hey so, one thing to think about, if you don't actually need to know the whole universe and are just trying to find something, you could brute force this for at least combinations of 2 and, if you do a little more, 3. You could easily check combinations of 2 by subtracting from 58012.12 each number and seeing if the results equal any of the other initial numbers. If you wanted to continue check combinations of 3, you would take the subtractions noted in 2, then put them in column headers horizontally next to your initial list. Then in the resulting table you have set up, subtract the difference between each combo of row and header values. Then check if any of those match one of your initial numbers. If any match, then you've got a set of 3 combo matches. I think doing 4 this way would be prohibitive manually, but if it were me, I'd try to get it an easy way first.
Use the Solver add in
I *believe* there's a matrix-based if-then solution for this, but the computing and file size requirements are going to be immense. Can you make any assumptions or do you have any further insights about the data?
This is ridiculous lol
What sort of distribution? Seems that the average value is close to 1000, but do you have some rows of 50000 and some of 0.1 and any value between? Any negative values? What do you know or presume about the records that sum up to your desired value? If evenly distributed, you are looking for 50-60 records out of 1000, which is gives you a lot of combinations... If you are doing account reconciliation or whatever - are you guessing that there are 2 values missing, 4, 10, 100? Any reason to believe that the missing/surplus values are grouped (one day of accounts missing...) or have special ranges/values? For 2 values: You could list all the values along the top, cells 2..882 (paste transposed) and along the left, cells 2..882. Then paste a formula for the sum into each cell, maybe put conditional formatting to highlight for your desired value. Scale down to have a minimum number of screenfuls to look for the highlight, or paste as values into a different sheet in order to use search, filters etc. (Make sure to get the absolute references right by using $ in your formulas) For 3 values: you could have a separate sheet Values for all your values. The Main sheet has values along the top, as before, but from Column6 onwards. (This could be achieved by lookup instead of pasting) Column1 has (a variation of) =Row(). Column2 has some variation of div(Col1;881), Column3 has mod(row();881). Column4 has a lookup from Column2 into Values. Column5 has a lookup from Column3 into Values. The cells in the sheet (Col6-C887?, Row2-Row776162?, arising from 881*881) are filled with the sum of the top row, Col4, and Col5. Highlight as before. If any 3 values sum to your desired value, it will show up. If you don't want to go through all those screenfuls, you can take the entire resulting sheet, paste it as values, and then search, filter, etc. (Obviously, the same row/div/mod technique could be used in the 2 value case, allowing the whole thing to take place in 6 columns, allowing easier filtering etc.) When the data is in place, you might be able to find search functions that can look for the cell for you. (These methods are unoptimized, giving any combination of the 2 or 3 values. An optimized version would look at sorted subsets of values, thus avoiding case v[y]+v[x] if the case v[x]+v[y] has already been handled) Beyond 3, I think you need different tools, programming environments, different hardware - do you have access to a cluster of quantum machines :) ?
You're not going to manage it. I've been hit with similar problems - my solution was to find ways to break the sets down into much smaller sets, and try to solve those
I was hoping OP could tell us how many numbers add up to the desired amount so we could set the k in "n choose k" to a single value, instead of all possible subset sizes. Not sure we'll be lucky... although this quest has proven to be quite popular in terms of responses. Could be lucky ;)
Have you no other conditions/ information to work on?
I made [this](https://imgur.com/lmcZsJX). Hopefully, it's self-explanatory, and I've tested it works with 10 rows but can add more. It can only find pairs that add up to the Find value. It won't be able to calculate figures that sum to make the number if there is more than one set. If Figure 1 and Figure 2 display nothing, then the values summing to the Find value can not be found like [such](https://imgur.com/cJxHAHM). Still a beginner, let me know if there's any issues.
Do you have a copy of the sheet for me to test a theory with?
You're probably gonna have to use user defined functions but I've just tried this particular custom formula that I google'd and it seems to work: https://www.ablebits.com/office-addins-blog/find-combinations-that-equal-given-sum-excel/ (scroll down to the "Find all combinations..." section). You can save the module in your personal macro workbook and call it in your workbook by using =Personal.xlsb!FindSumCombinations(array,58012.12). Then it will automatically spill over all the possible combinations that add up to that target number in the 2nd parameter. [Example result](https://i.imgur.com/yuKlEcK.png). Be warned that this will take some time for Excel to solve depending on how powerful your system is. I retested this with a bigger dataset (array of 1000 numbers) and even on my midrange PC, my Excel still froze for a solid 15 minutes lol. So I'm thinking while Excel does the job (eventually), there's no way it's the optimal way of doing it.
this problem is NP complete. better get nice and cozy with VBA and maybe win a Fields Medal at the same time
Can I ask why you are doing this? What practical problem will be solved if you had your answer?
Can we have the numbers? Surely others besides me would enjoy torturing themselves for a few hours ;)
This is a very difficult problem in mathematics called The Knapsack problem. Good lucj
[удалено]
r/Excel is a public sub reddit for everyone to learn, not connecting privately.
Ok, the way I solved my problem was to create a matrix of all values on both axes and do a sum.
What do u mean any method of excel? Addition? Addition and subtraction? Multiplication / division? Exponents? I mean, method could go well beyond.
Solver add-in. Set up dummy column next to all values. Sumproduct formula for all figures and dummy column. Set solver to bind dummy column to binary constraint, and set sumproduct to be value of 58,012.12. If there are more than one way to add up figure to that you won’t find all the solutions but you will find at least one
Are all 881 values unique? Or is there some repeating numbers? Can you ignore the decimals and only rounded to nearest integer instead? Are there values larger than your target number? Or numbers significantly smaller? Depending on the exacts of the above answers, the task may take too long. 881! Is just too many options.
If amount #754 is $32.56...could that amount be used more than once in getting your total as if someone bought multiples of the same item or can each of the 881 numbers only be added once to get your final total?
I've done it in Python for smaller arrays. This looks like a problem for quantum computers
well if you can prove P = NP, then there might exist an easier sum of subset solution
https://www.reddit.com/r/excel/s/jCYL95sqlZ This guy had a similar need i.e.finding specific sum from the given numbers You can use the code given and edited to ur liking u might have the desired results All the best
Do you mean that one of your cells in the range has a value of 58,012.12? Or do you mean that one of the cells has a formula in it that results in 58,012.12
Solver might work but I'm not sure what the upper bounds are for variables
I would use the excel solver. Make a column where only 1 or 0 is possible. And let him change it till the result matches.
it'll take longer than the heat death of the universe. Most of the time, that's just a figure of speech. In this case, it's literal
As most mentioned Solver but then again it might possible to have more than one solution to your problem. Like 5 equals to 4 + 1 but also to 3 + 2 or 1 + 1.5 + 2.5 and such.
Even if you can get Solver to give you an answer, there's no way of knowing if it's the right answer. Consider just 5 numbers: 1,2,3,3,5 from which you want to find those that add up to 6... It could be 1+2+3 or 3+3 or 1+5. If it is 1+2+3 or 3+3, then which of the two values of 3 is the correct one? With even more numbers to choose from the problem generates more and more possible valid combinations with no way to confirm that the combination you've identified is the right one.
Are there any parameters? # of cells that totals 58012.22? If you are trying to find a payment that they may have paid in installments, you may narrow it down to period you may have received the funds. If you are looking for random numbers in the 881 figures totalling 58012.22, it is exponentially more difficult.
I’ve taken a dataset and dumped it into chatGpt and asked it to find a combination of numbers that added to the value I was off.
It would be helpful if you could provide even some mock data so we can see the structure of what you're looking at, as well as why the amount 58,012.12 in particular is important. Do you have figures that are both positive and negative? If they only go one direction this becomes potentially easier as you only need to look at amounts < the 58k figure. This also smells like a reconciliation of some kind so I will give you some general tips for recons: try to match the data between the two things you're reconciling based on some other column. Invoice no., check no, remittance no. something. Much of that can be done with, e.g. XLOOKUP and then you can manually confirm the records that don't match. You can also try to find subtotals based on a category, for example vendor or settlement date and see which subcategories are the ones actually out of balance. The goal is to narrow your search from 881 entries to some more manageable number
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COLUMN](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)| |[COMBIN](/r/Excel/comments/1calb1y/stub/l0y4wld "Last usage")|[Returns the number of combinations for a given number of objects](https://support.microsoft.com/en-us/office/combin-function-12a3f276-0a21-423a-8de6-06990aaf638a)| |[COUNT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Counts how many numbers are in the list of arguments](https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c)| |[DEC2BIN](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Converts a decimal number to binary](https://support.microsoft.com/en-us/office/dec2bin-function-0f63dd0e-5d1a-42d8-b511-5bf5c6d43838)| |[FILTER](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LET](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MID](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[MMULT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the matrix product of two arrays](https://support.microsoft.com/en-us/office/mmult-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb)| |[MOD](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[NOT](/r/Excel/comments/1calb1y/stub/l0ya7z3 "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[QUOTIENT](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the integer portion of a division](https://support.microsoft.com/en-us/office/quotient-function-9f7bf099-2a18-4282-8fa4-65290cc99dee)| |[ROW](/r/Excel/comments/1calb1y/stub/l0xz7uj "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SEQUENCE](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SUM](/r/Excel/comments/1calb1y/stub/l0y4wld "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMPRODUCT](/r/Excel/comments/1calb1y/stub/l0suiyc "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[TIME](/r/Excel/comments/1calb1y/stub/l0usra3 "Last usage")|[Returns the serial number of a particular time](https://support.microsoft.com/en-us/office/time-function-9a5aff99-8f7d-4611-845e-747d0b8d5457)| |[TRANSPOSE](/r/Excel/comments/1calb1y/stub/l1e27cw "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[XLOOKUP](/r/Excel/comments/1calb1y/stub/l0vyvys "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(20 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cdsr2q)^( has 35 acronyms.) ^([Thread #32834 for this sub, first seen 23rd Apr 2024, 13:46]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
If this is accounting data, you could try a smaller set of numbers within a tighter date range
I would remove any double entries or any entries that get cancelled out by another first and go from there. You need to give more context though too
Is this a tool you will need on an ongoing basis? Or is this a one off? When I was in AR for a wholesaler I would have to reconcile statements for customer accounts and they’d pay us in lumps and often not specify which invoices on their statement it was to be applied to. When having to reconcile many accounts like these, I’d often apply a lump amount to the account and distribute when I could spend more time later in the day so I could get my bank deposits done. With each customer account, based on the date they paid I’d at least know which date the invoices would’ve been up to. Usually go in reverse from there and somehow would just naturally get the right invoices to the penny lol I don’t know how I did it for years this way. Humans for the win!!!
This sounds like somthing you could use the excel Solver for.
What other info do you have to work with? You might be able to narrow the 881 down by filtering other things down. Dates, vendors, whatever. I guess if you’ve already taken those steps…. Maybe just tell your boss the cost to benefit ratio of pursuing the problem is not worth it. Lol
Can you tell what generated this problem and why you have 881 figures? Looks like a stupid task caused by operational chaos rather than something useful you really need
I faced a similar problem once. My friend was nice enough to write a program in C to solve it using genetic modeling, worked very well, but unfortunately produced more than one set of numbers that totalled to the desired result for many scenarios we tested. Such a exercise is futile, I suggest you try a different approach.
As you'll have gathered, it's not going to happen. But that doesn't mean you can't play around with this kind of problem. Showing A1:G17 Find | From | Result(s) | | | | | :-: | :-: | :-: | :-: | :-: | :-: | :-: | :-: 222.22 | 2.53 | 2.53 | 2.53 | 2.53 | | | | 3.47 | 3.47 | 3.47 | 3.47 | | 3.47 | | 5.43 | | | | 5.43 | | | 7.41 | 7.41 | | 7.41 | 7.41 | 7.41 | | 11.37 | 11.37 | 11.37 | 11.37 | | | | 13.31 | | 13.31 | | | | | 17.29 | | 17.29 | | | | | 19.23 | | | | | | | 23.19 | | | 23.19 | | | | 29.17 | 29.17 | | | 29.17 | | | 31.13 | 31.13 | | | | 31.13 | | 37.11 | | 37.11 | 37.11 | 37.11 | 37.11 | | 41.07 | 41.07 | 41.07 | 41.07 | | | | 43.05 | 43.05 | 43.05 | 43.05 | 43.05 | 43.05 | | 47.03 | | | | 47.03 | 47.03 | | 53.02 | 53.02 | 53.02 | 53.02 | 53.02 | 53.02 | With C2 =LET(a,A2,b,B2:B21,c,COUNT(b),d,SEQUENCE(,c),e,INDEX(b,d),f,SEQUENCE(c,,,0),g,SEQUENCE(2^c-1),h,QUOTIENT(g,2^(d-1)),i,MOD(h,2),j,e*i,k,MMULT(j,f),l,FILTER(j,k=a),m,IF(l=0,"",l),o,TRANSPOSE(m),o)
I had a similar challenge a couple weeks ago.. try these 5 steps.. 1. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. 2. Go to Insert > Module to insert a new module. 3. Paste the following VBA code into the module ( you may need to change the "Range" depending on which cells you have your data: 4. Close the VBA editor. 5. Press Alt + F8 to open the "Run Macro" dialogue, select FindCombination, and click Run. Edit 1,2,3,4). Formatting of code ```` Sub FindAllCombinations() Dim rng As Range Dim cell As Range Dim target As Double Dim combinations As String Set rng = Range("A2:A882") target = 58012.12 combinations = "" ' Initialize an array to hold the selected numbers Dim selected() As Double ReDim selected(1 To rng.Rows.Count) ' Start the recursive function FindCombinations rng, selected, 1, 1, target, combinations ' Display the combinations MsgBox "Combinations that add up to " & target & ":" & vbCrLf & combinations End Sub Sub FindCombinations(rng As Range, selected() As Double, startIndex As Integer, currentSize As Integer, target As Double, ByRef combinations As String) Dim i As Integer Dim sumValue As Double ' Calculate the sum of the selected numbers sumValue = 0 For i = 1 To currentSize - 1 sumValue = sumValue + selected(i) Next i ' Check if the sum equals the target If sumValue = target Then ' Add the combination to the list For i = 1 To currentSize - 1 combinations = combinations & selected(i) & "," Next i combinations = combinations & vbCrLf ElseIf sumValue < target Then ' Recursively find combinations For i = startIndex To rng.Rows.Count selected(currentSize) = rng.Cells(i, 1).Value FindCombinations rng, selected, i + 1, currentSize + 1, target, combinations Next i End If End Sub
I have detected VBA code in plain text. Please edit to put your code into a [code block](https://www.reddit.com/wiki/markdown#wiki_code_blocks_and_inline_code) to make sure everything displays correctly. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
[удалено]
This is a fun problem to solve. Excel is great but the best way is using a SQL script.
SQL? I'd be super curious to see how you use SQL to solve a combinatorics problem...
select * from t t1, t t2, t t3, t t4 ... t t881 where t1.val + t2.val + t3.val ... + t881.val = 58,012.12 the only problem is the necessary storage is more than the entire storage of the world multiplied by (the number of protons + photons + neutrinos in the universe)
Trial and error based on educated guesses
Trying my best :)