Check sum of two values is 0 and update the status

[Shivam_Rana]

Hello everyone,

I have attached an excel file with Sheet1 , Sheet2 and Sheet3 , each Sheet has its own Output Sheet (for reference or understanding).

I have added a note comment to the Output Sheets indicating what actions we need to do.
Please let me know if there is anything i can do.
TestFile.xlsx (14.4 KB)

[Shivam_Rana]

Awaiting for the help

[Yoichi]

Hi,

For now, the following is solution for Sheet1. Can you try this?

dt.AsEnumerable.GroupBy(Function(r) r("Invoice").ToString).ToList().ForEach(Sub(g)
    If (g.Sum(Function(r) CInt(r("amount").ToString))=0) Then
           g.ToList.ForEach(Sub(r2) r2("Status")="Match")
    Else
    		g.ToList.ForEach(Sub(r2) r2("Status")="Open Item")
    End If
End Sub
)

Sample20230625-1aL.zip (13.7 KB)

Regards,

[Yoichi]

Solution for sheet2

Please check sequence.xaml

Sample20230625-1aLv2.zip (15.8 KB)

[Shivam_Rana]

@Yoichi Thanks for the flow, Really appreciated

For Purchase Invoice and Payments Combination , if the Invoice no. is matched then the sum of Amount Column should be 0 and Update the Status as Match else Update Open Item in Status.

I just changed the value of Category Column for row 14 from purchase invoice to payments.
But still it is showing it as Match.
But as per the condition it is open item.

[Yoichi]

Hi,

What should we think when Payment has plus value? Does it it should be written minus value?

Regards,

[Shivam_Rana]

@Yoichi
Sometimes the payments value is refunded and so Amount value can be positive😊

Also for Sheet 2 we have to first check the combination of A and B value for Category column.
Here also i have replaced the value from A to B for row 14 and still it is showing as Match

[Yoichi]

Hi,

@Shivam_Rana

Sometimes the payments value is refunded and so Amount value can be positive

In this case, total of Invoice 666632423 is 0 isn’t it?

Regards,

[Shivam_Rana]

@Yoichi
that i am not sure.

Can’t we set a rule such that the bot first creates a combination of purchase invoice and payments, filters the matched invoice, and then verifies that the sum of the amount column is zero and updates the status?

[Yoichi]

Hi,

If we can allow to check combination of a row and next row, it’s possible like Sheet2. However as invoice number is not ordered in this sheet, it’s complicated I think.

The following is modified sample with checking category for Sheet2.

Sequence.xaml (18.1 KB)

Regards,

[Shivam_Rana]

Thanks @Yoichi for your kind support
For Sheet 2 the flow is working really good.

Can you please share the flow for Sheet 3?

[Yoichi]

Hi,

@Shivam_Rana

Can you please share the flow for Sheet 3?

How about the following?

Dim arrWords As String() =dt.AsEnumerable.Select(Function(r) r("Category").ToString).ToArray()
dt.AsEnumerable.GroupBy(Function(r)  arrWords.Select(Function(s)  System.Text.RegularExpressions.Regex.Match(r("Category").ToString,s).Value).Where(Function(s) s<>"").OrderBy(Function(s) s.Length).First()).Where(Function(g) g.Count>1).ToList.ForEach(Sub (g)
If g.Sum(Function(r) CInt(r("Amount").ToString))=0 Then
g.ToList.ForEach(Sub(r2) r2("Status")="Match")
Else
	g.ToList.ForEach(Sub(r2) r2("Status")="Open Item")
End If
End Sub
)

Sequence1.xaml (6.0 KB)

Regards,

[Shivam_Rana]

@Yoichi

Thank you so much it is working really well.
But it doesn’t update the status as open item, only match value is updating.
I have added few row items in my file to check this scenario.

[Yoichi]

How about the following sample for sheet3?

Sequence1.xaml (6.1 KB)

[Shivam_Rana]

@Yoichi Got the correct output Thanks