Validation_Both_Tables

[Balachander_Pandian]

Hi Expert,
I have an excel file which contains two sheets with same table. I want to validation to both sheets in the columns of Total amount with sum. If both tables sum of total amounts are the same then the process will go to the next work flow. If sum of total amounts are not same exception will occur. How can we achieve this by LinQ to put condition? Attached sample input here.
Validation.xlsx (9.7 KB)

[lrtetala (Lakshman Reddy)]

Hi @Balachander_Pandian

totalSumSheet1 = (From row In DT1.AsEnumerable()
                  Select Convert.ToInt32(row("Total Amount"))).Sum()

totalSumSheet2 = (From row In DT2.AsEnumerable()
                  Select Convert.ToInt32(row("Total Amount"))).Sum()

If totalSumSheet1 = totalSumSheet2 Then
    ' Proceed to the next workflow
Else
    ' Throw an exception or handle the error
    Throw New Exception("The sums of the Total Amount columns in the two sheets do not match.")
End If

totalSumSheet1,totalSumSheet2 datatype: Int32

Output:

Regards,

[rlgandu (Rajyalakshmi Gandu)]

@Balachander_Pandian

totalAmount1 = dataTable1.AsEnumerable().Sum(Function(row) Convert.ToDecimal(row("TotalAmount")))
totalAmount2 = dataTable2.AsEnumerable().Sum(Function(row) Convert.ToDecimal(row("TotalAmount")))
totalAmount1 = totalAmount2

[pravallikapaluri]

Hi @Balachander_Pandian

' Step 1: Read the Excel sheets into DataTables
dtSheet1 = Read Range activity to read "Sheet1"
dtSheet2 = Read Range activity to read "Sheet2"

' Step 2: Calculate the sums using LINQ
SumSheet1 = dtSheet1.AsEnumerable().Sum(Function(row) Convert.ToDecimal(row("Total Amount")))
SumSheet2 = dtSheet2.AsEnumerable().Sum(Function(row) Convert.ToDecimal(row("Total Amount")))

' Step 3: Compare the sums
If SumSheet1 = SumSheet2 Then
    ' Continue with the next workflow
Else
    ' Throw an exception
    Throw New Exception("The sums of the Total Amount columns in the two sheets are not equal.")
End If

[Nawazish_Ahmad]

Hi @Balachander_Pandian

Try below approach:

1. Read Range Activity: Read both Sheet1 & Sheet2, assigned in Variable as DT1 and DT2.
2. IF Activity: Use below expression for checking the Total Amount Sum is same or not for both the Sheets.

DT1.AsEnumerable.Sum(Function(x) Convert.ToDecimal(x("Total Amount"))).Equals(DT2.AsEnumerable.Sum(Function(x) Convert.ToDecimal(x("Total Amount"))))

---

---

Hope it helps!!
Happy Automation

[Balachander_Pandian]

@pravallikapaluri Thank you so much

[Balachander_Pandian]

@Nawazish_Ahmad , I have an another condition with same input file that total amount comes with Decimal points. Also, bot will consider to proceed with further workflow if sum of total amounts are comes with difference value up to 0.01 to 0.99 Plus or minus. if difference values go beyond the limit, bot will throw the exception. How can achieve this same LINQ?