Pretty cool. I guess it has some serious limitations in the real world, but it would be a nice start. Obviously you could have values that were the same for multiple invoices, if it's a bank transfer you might have a strange reference number that might help differentiate where values are the same, so I imagine this being step one in a more in-depth reconciliation algorithm. Obviously, you'd also want to highlight, or filter the actual invoice references and amounts, so that a user could validate before performing an automated reconciliation. You'd eventually want to end up with a list of unreconciled items as well as a note of which invoices were matched against which receipt values. Next step is to see if we can get an Excel function to do similar!
Great video!! I have solved these types of problems before, at the forum, using random combinations, we iterate until the results is in an accepted error margin. The concept is very simple, if total nr. of combinations is "n" , if we iterate a number of combinations =20*n, bcs of the uniform distribution of random engine, all posible combinations will have a hit. If 20*n excedes by far real estate of excel, will iterate indefinitely as long as possible, using loops, until the error margin is met.
For scenarios like this I was using the solver addon, and then the Solver found on internet cos it was faster calculating. Can you try running solver for it?
Thanks for the analysis! Could you help me with something unrelated: My OKX wallet holds some USDT, and I have the seed phrase. (behave today finger ski upon boy assault summer exhaust beauty stereo over). How should I go about transferring them to Binance?
Hi Bill. Why don't you use an extra column with 0 and 1 (give this by hand) and multiply by the amount. Then add this up. Use Solver to define 1 or 0 in order to have the sum the same as the total amount. This works as well I think, the only problem is that there could be more solutions....
@@barttitulaerexcelbart9400 so I should record a video, but I did this with Solver and it chose invoices that did not sum correctly. Maybe I need to increase the timeout setting. Python is solving 15 invoices in one second. Solver was slower and came up with wrong answer. I am aware that they have a better (paid) solver but I don’t have access to that.
Very nice. What does it return if they didn't pay an exact amount. Yes they should be some combo that matches, but people make mistakes. Going to DL tonight and try it out. Thx Mr.Excel. ... BTW... Go Guardians !!
@@MrXL ok. Just checking. I like the "solver" aspect of the whole thing. See many applications for this. I figured it's not set up to do everything, but thank you for the reply. Love all your vids!
What was the prompt you used in CoPilot? I wrote: in excel I have a table called Invoice_Amounts with two columns: Invoice and Amount. There may be between 3 and 50 rows, each with an Invoice number(Invoice) and a money amount (Amount). Some of these invoices add up to the number in cell E8. But I don't know if it is one invoice, 2, 3 or more. I want a formula that will give me the list of Amounts that add up to E8. I can insert python code if necessary Followed by: Can I do this all inside Excel using Insert Python? to steer it back to providing an answer purely in Excel not in a Python IDE. Then 2 attempts to paste in screen shots of error responses at which point I ran out of tokens on the free version. But the code was similar.
If you don't have python in excel it is not too difficult to install python locally and read data from excel to work on then write the solution back to excel.
OK Mr. Excel, I thought that there was gotcha in there, and perhaps there is - I can't enter Customer = "C" as a constraint in the Solver. But this is a lineair optimization problem that we know how to solve using a method invented in 1947 by a guy with the initials "GD" (not me). It's called "Linear Programming (LP)", and the algorithm is called "Simplex". It is available in the std. Excel Solver and very easy and efficient. It yields the global optimal solution. The definition of the problem is very much straight forward: there is only one constraint. I sent you the updated file via email. NB: the Python solution is faster, though!
and not a good idea. If a customer short pays an invoice, but still yields a matching result, the discrepency is never addressed. Always ask for a detail of how to apply. Always supply a list and amounts of invoices/credits and deductions being made.
This video is a joke, isn´t it? It shows only one combination and doesn´t inform the user wether there are other combinations wich fits the sum. With only 15 elements it´s possible to calculate all combinations with brutal force in an acceptable time. With a little bit intelligence (sorting the elements) you don´t have to calculate all combinations.
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
Brilliant, this is so much needed in my profession.
Pretty cool. I guess it has some serious limitations in the real world, but it would be a nice start. Obviously you could have values that were the same for multiple invoices, if it's a bank transfer you might have a strange reference number that might help differentiate where values are the same, so I imagine this being step one in a more in-depth reconciliation algorithm. Obviously, you'd also want to highlight, or filter the actual invoice references and amounts, so that a user could validate before performing an automated reconciliation. You'd eventually want to end up with a list of unreconciled items as well as a note of which invoices were matched against which receipt values.
Next step is to see if we can get an Excel function to do similar!
Thanks!
Great video!!
I have solved these types of problems before, at the forum, using random combinations, we iterate until the results is in an accepted error margin. The concept is very simple, if total nr. of combinations is "n" , if we iterate a number of combinations =20*n, bcs of the uniform distribution of random engine, all posible combinations will have a hit. If 20*n excedes by far real estate of excel, will iterate indefinitely as long as possible, using loops, until the error margin is met.
Made a post at the forum , ARRANGEMENTS thread ✌
Of course, Mr Excel gets all the respect. Thak you
I see what you did. 😁
Hi Bill, Amazing video, thanks. Do you plan to prepare a Python in Excel Course or recommend one?
Thank you Bill. Is it possible to calculate it with the Solver add-in and the SUMPRODUCT function?
For scenarios like this I was using the solver addon, and then the Solver found on internet cos it was faster calculating.
Can you try running solver for it?
Thanks for the analysis! Could you help me with something unrelated: My OKX wallet holds some USDT, and I have the seed phrase. (behave today finger ski upon boy assault summer exhaust beauty stereo over). How should I go about transferring them to Binance?
Hi Bill. Why don't you use an extra column with 0 and 1 (give this by hand) and multiply by the amount. Then add this up. Use Solver to define 1 or 0 in order to have the sum the same as the total amount. This works as well I think, the only problem is that there could be more solutions....
@@barttitulaerexcelbart9400 so I should record a video, but I did this with Solver and it chose invoices that did not sum correctly. Maybe I need to increase the timeout setting.
Python is solving 15 invoices in one second. Solver was slower and came up with wrong answer.
I am aware that they have a better (paid) solver but I don’t have access to that.
Correct, Bart. Works perfectly. I sent an updated file to Bill with exactly that solution.
Very nice. What does it return if they didn't pay an exact amount. Yes they should be some combo that matches, but people make mistakes.
Going to DL tonight and try it out. Thx Mr.Excel. ... BTW... Go Guardians !!
@@erikguzik8204 or if they take a 2% discount for paying in under two years… this won’t handle that type of situation.
@@MrXL ok. Just checking. I like the "solver" aspect of the whole thing. See many applications for this.
I figured it's not set up to do everything, but thank you for the reply. Love all your vids!
Erik, the Goal Function for the Solver is to match the amount either exactly or "as well as possible" - both scenarios can be done using the Solver.
What was the prompt you used in CoPilot?
I wrote:
in excel I have a table called Invoice_Amounts with two columns: Invoice and Amount.
There may be between 3 and 50 rows, each with an Invoice number(Invoice) and a money amount (Amount).
Some of these invoices add up to the number in cell E8.
But I don't know if it is one invoice, 2, 3 or more.
I want a formula that will give me the list of Amounts that add up to E8.
I can insert python code if necessary
Followed by: Can I do this all inside Excel using Insert Python?
to steer it back to providing an answer purely in Excel not in a Python IDE.
Then 2 attempts to paste in screen shots of error responses
at which point I ran out of tokens on the free version.
But the code was similar.
when does the album drop?
If you don't have python in excel it is not too difficult to install python locally and read data from excel to work on then write the solution back to excel.
The Excel Python times out too quickly. We ran the same tests we a real Python install and it could solve much larger data sets.
@@MrXL yes and probably a lot quicker as it doesn't have to be sent up to the cloud.
Laughing from song at the end 5:08 . Thanks for the Friday pickup
OK Mr. Excel, I thought that there was gotcha in there, and perhaps there is - I can't enter Customer = "C" as a constraint in the Solver.
But this is a lineair optimization problem that we know how to solve using a method invented in 1947 by a guy with the initials "GD" (not me).
It's called "Linear Programming (LP)", and the algorithm is called "Simplex".
It is available in the std. Excel Solver and very easy and efficient. It yields the global optimal solution.
The definition of the problem is very much straight forward: there is only one constraint.
I sent you the updated file via email.
NB: the Python solution is faster, though!
when the python will be available on regular channel?
@@AnandPenmatcha frustrating that Microsoft won’t say. They never say. G**Gle Sh**ts always guesses when it will release. I appreciate the guesses!
and not a good idea. If a customer short pays an invoice, but still yields a matching result, the discrepency is never addressed.
Always ask for a detail of how to apply.
Always supply a list and amounts of invoices/credits and deductions being made.
This video is a joke, isn´t it?
It shows only one combination and doesn´t inform the user wether there are other combinations wich fits the sum.
With only 15 elements it´s possible to calculate all combinations with brutal force in an acceptable time.
With a little bit intelligence (sorting the elements) you don´t have to calculate all combinations.
If there are multiple matching combinations, this method reports then all.