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

[amorej]

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.

[dreldrel]

You can use a summarize tool to easily help you sum up your invoice. If this helps, feel free to like the comment and select as the solution

[amorej]

The SUM tool will just add up all the invoices.  I am looking for Alteryx to choose invoices that will = cash received.   for example. 

Invoices

10

23

35

41

50

Cash received = $95.  I want Alteryx to calculate the invoices that will sum to cash received.

[jrlindem]

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

[Cole]

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

[JAKupchik]

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