Does anyone have a solution to mimic the Solver Tool used in excel

Question

I am using Designer Desktop.  I have a set of invoices by date.  We received cash for most of these invoices.   I want Alteryx to sum up all the invoices and tell me which invoices add to the sum of the cash received.  I have attached how I solve in excel.  However, Excel is only limited to 200 rows for this function, and I can only do 1 date at a time to get results.  Has anyone ever solved for this before?  I am not even sure where to start.

Solver Example for Alteryx.xlsx

JAKupchik · Answer

amorej,

I agree - my Alteryx Solver template runs smoother and faster than its Excel counterpart, as the number of records does not need defined/re-configured.

I did notice that you're using the GRG Nonlinear method when Simplex LP is likely more appropriate and will run much faster.

Best,

John

Cole · Answer

It sounds like the optimization tool would do the trick.  You should check out this template created by @JAKupchik .

Re: Optimization of output in Alteryx (excel solve... - Alteryx Community

jrlindem · Answer

You could consider building an iterative macro to sum lines until it reaches a target amount and then it will reset to start counting again for the next target amount.

Or if you have everything nice and lined up, you could probably also accomplish this using a multi-row formula to total up rows until it meets or exceeds a value from another cell.

Just some inspiration to try and/or research.  Hope that helps, -Jay