Combination of sum

[Akhil_Jose1 (Akhil Jose)]

I have one data table DT1 & one variable contains the total amount.

I have to keep taking the sum from the DT1 (using various combinations) until it equals the total amount.

Example:

             DT1
         Amount                 total amount = 25
              10                         
              11
              15
              40

Also, need to take the rows which gives the exact sum.

[Lalit_Chaudhari]

Hi @Akhil_Jose1,

You can use the following logic as a possible solution:
start sum = 0
for each row in datatable
sum= sum + row(“Amount”)
if sum > total amount :
sum= sum - row(“Amount”)
if sum = row(“Amount”):
break

Hope This helps You

[yedukondaluaregala]

@Akhil_Jose1

1. Create a assign activity and name it as intTotalAmount.

2. Take For Each Row in Datatable activity

3. Pass your data table variable

4. Inside loop add one assign activity in that to section pass above intTotalAmount variable and in Value section If(IsNumeric(CurrentRow(“ColumnName”).ToString.Trim),intTotalAmount +CInt(CurrentRow(“ColumnName”).ToString.Trim),intTotalAmount)

5. After this add an IF activity

6. in that if activity check your condition
   intTotalAmount = Totalamount

7. in then section use Break activity.

Follow these steps.

[Mir.Jasimuddin]

Hi @Akhil_Jose1 , you can try the bellow code using invoke code.

1. Extract amounts:

   listAmounts = DT1.AsEnumerable().Select(Function(r) CInt(r("Amount"))).ToList()
   

2. Use Invoke Code (VB.NET) to find all combinations that sum to total (e.g., 25):

   result = New List(Of List(Of Integer))
   Sub Find(start As Integer, target As Integer, current As List(Of Integer))
       If target = 0 Then result.Add(New List(Of Integer)(current)) : Return
       For i = start To listAmounts.Count - 1
           If listAmounts(i) <= target Then
               current.Add(listAmounts(i))
               Find(i + 1, target - listAmounts(i), current)
               current.RemoveAt(current.Count - 1)
           End If
       Next
   End Sub
   Find(0, 25, New List(Of Integer))
   

3. result will have all row combinations summing to the total. Use it to get matching rows from DT1.

[Anil_G (Anil Gorthi)]

@Akhil_Jose1

you can use this linq

Enumerable.Range(1, CInt(Math.Pow(2, dt.Rows.Count)) - 1).Select(Function(index)dt.AsEnumerable().Where(Function(row, i) (index And (1 << i)) <> 0).ToList()).Where(function(x) x.Select(function(y) Cint(y(0))).Sum.equals(25))

here dt is the datatable and assumption is first column contains numbers and 25 is the sum it check ..you can add variable in place of it or anything

also first to know if already one combination has sum better use .count>0 then get the first combination..this give multiple combiantions if available use can use first or (0) to get the first combination

Hope this helps

cheers

[Akhil_Jose1 (Akhil Jose)]

Hi Anil,

Thanks for the linq.

I need to take the second combination or third etc..

how to take it from the itemarray.

Currently I’m able to take the first value using “result.First().First().ItemArray(0).ToString()”.

[Anil_G (Anil Gorthi)]

@Akhil_Jose1

its a list of list of datarows

so if you need say second match of lists from that second match say you need third item…then you use this

result(1)(2).itemarray(0).ToString

1 is for second set and 2 is for 3rd element or row in the second set

as the datatable is assumed to contain first column as required column, always itemarray(0) would never change

Hope this helps

cheers

[Akhil_Jose1 (Akhil Jose)]

Got it

Thanks Anil!

[Akhil_Jose1 (Akhil Jose)]

Hi Anil,

It is perfectly working fine with smaller data tables but for larger data tables throwing this error.

Kindly help

[Tapas_Kumar_Pattnaik (Tapas)]

Hi @Akhil_Jose1

replace CInt(...) with CLng(...) to handle larger numbers.

CInt(int 32) can handle less data whereas Long type can handle more number of data.

If it resolves kindly mark it as solution.

All the best

[Akhil_Jose1 (Akhil Jose)]

Hi Tapas,

I have tried the same.

Not working, the same error is happening again.

[Tapas_Kumar_Pattnaik (Tapas)]

I see. this might be because Math.Pow and Enumerable.Range , which are limited by 32.
for this i would recommend you to use script like the Invoke code using VB or C#.

Can you share the value passed inside result variable?

[Akhil_Jose1 (Akhil Jose)]

@Tapas_Kumar_Pattnaik

VB or C#.

-137.99
-128.27
-33.59
-142.34
-142.34
-88.65
-7.19
-5.94
-429.66
-613.09
-229.79
-179.99
-14.63
-136.29
-144
-347.88
-377.31
-499.98
-383.36
-74.46
-126.93
-138.2
-164.78
-38.34
-300
-18.48
-74.46
-285.78
-295.59
-15.68
-107.23
-7.69
-167.94
-398.35
-18.6
-98.32
-9.48
-74.46
-323.94
-70
-94.74
-116.47
-18.48
-393.6
-100.72
-103.32
-25.24
-141
-27.68
-35.58
-33.54
-529.1
-747.73
-1271.46
-292.43
-73.39
-259.73
-179.99
-357.59
-299.94
-82.14
-357.59
-74.46
-500
-56.36
-647.93
-408.95
-81
-78.51
-99.95
-25.47
-749.95
-723.12
-6000

Enumerable.Range(1, CInt(Math.Pow(2, dt.Rows.Count)) - 1).Select(Function(idx) dt.AsEnumerable().Where(Function(row, i) (idx And (1 << i)) <> 0).ToList()).Where(Function(x) x.Select(Function(y) CDec(y(12))).Sum() = amount)

y(12) - column index → contains the above values

[Tapas_Kumar_Pattnaik (Tapas)]

No, i meant the result variable u have used in the assign activity need its value..

Could share value mentioned inside the Value to save

[Tapas_Kumar_Pattnaik (Tapas)]

@Akhil_Jose1

Enumerable.Range(1, CInt(Math.Pow(2, dt.Rows.Count)) - 1).Select(Function(idx) dt.AsEnumerable().Where(Function(row, i) (idx And (1 << i)) <> 0).ToList()).Where(Function(x) x.Select(Function(y) CDec(y(12))).Sum() = amount)

//Inputs: dt (DataTable), amount (Decimal)
//Output: matchingCombos (List(Of List(Of DataRow)))

Dim matchingCombos As New List(Of List(Of DataRow))()

Sub FindCombinations(index As Integer, currentCombo As List(Of DataRow), currentSum As Decimal)
    If currentSum = amount Then
        matchingCombos.Add(New List(Of DataRow)(currentCombo))
        Return
    End If
    If index >= dt.Rows.Count OrElse currentSum > amount Then
        Return
    End If

    // Include current row

    currentCombo.Add(dt.Rows(index))
    FindCombinations(index + 1, currentCombo, currentSum + Convert.ToDecimal(dt.Rows(index)(12)))
    currentCombo.RemoveAt(currentCombo.Count - 1)

    //Exclude current row
    FindCombinations(index + 1, currentCombo, currentSum)
End Sub

FindCombinations(0, New List(Of DataRow)(), 0D)

in the invoke code activity use below variables and its type |

• dt as InArgument ( for this variable pass your DataTable variable )
• amount as InArgument (for this variable pass your target sum, create a variable in variable panel as amount of Int then assign it a value, e.g., 25.)
• matchingCombos as OutArgument (List(Of List(Of DataRow))) → your output argument.

Please try above reference and let us know your result.

[Akhil_Jose1 (Akhil Jose)]

Hi Tapas,

I got this error

I have also passed the variables dt & amount.

[Tapas_Kumar_Pattnaik (Tapas)]

Could you please click on the Edit Argument and share the screenshot. Along with what values you have passed in each arguments?

[Akhil_Jose1 (Akhil Jose)]

Hi Tapas,

currently I’m using the below code.

It is working fine & finding the match, but it is not stop running if there is no match.

’ Copy UiPath arguments into local vars
Dim localDT As DataTable = dt
Dim targetAmount As Decimal = bank_amount
Dim results As New List(Of List(Of DataRow))()

Dim totalRows As Integer = localDT.Rows.Count

Dim RecursiveFind As Action(Of Integer, List(Of DataRow), Decimal) = Nothing

RecursiveFind = Sub(index As Integer, currentCombo As List(Of DataRow), currentSum As Decimal)
If currentSum = targetAmount Then
results.Add(New List(Of DataRow)(currentCombo))
Return
End If

If index >= totalRows OrElse currentSum > targetAmount Then
    Return
End If

' Include current row
currentCombo.Add(localDT.Rows(index))
RecursiveFind(index + 1, currentCombo, currentSum + Convert.ToDecimal(localDT.Rows(index)(12)))
currentCombo.RemoveAt(currentCombo.Count - 1)

' Exclude current row
RecursiveFind(index + 1, currentCombo, currentSum)

End Sub

’ Start recursion
RecursiveFind(0, New List(Of DataRow)(), 0D)

’ Write result back to output argument
matchingCombos = results

[Tapas_Kumar_Pattnaik (Tapas)]

Try add a check after the recursion to determine if any matches were found, and take appropriate action.

=========================================

//Start recursion
RecursiveFind(0, New List(Of DataRow)(), 0D)

// Return empty list or a custom message if no match is found
If results.Count = 0 Then
//Return an empty list
matchingCombos = New List(Of List(Of DataRow))()
LogMessage(“No matching combination found.”)

Else
matchingCombos = results // this means results is null in UiPath u can use if condition and pass condition matchingCombos.Count = 0 and in the statement pass what is your requirement.

End If

=========================

Try this and let us know your output.

All the best