Excel - Sum rows and check result

Hello,

I have a excel file with this kind of structure

Name Jan Fev Mar Apr May Jun Jul Aug Sep Oct Nov Dec RESULT
Emma 2 4 5 3 2 2 1 0 2 1 4 1 27
John 1 0 1 5 5 3 1 1 3 0 2 2 24
Paul 3 2 3 0 1 5 0 0 5 0 3 5 27

I would like to check if the sum of reach row per name is equal to the total in column Result
I could have several names/rows
I do a Read Range to read the dt with names and from col Jan to column Dec: dt_Annual
I create a second dt_Result with only column Result
First I need to check if the sum from Jan to Dec is what is written in col RESULT
Second if what is written in col RESULT is not correct, I need to update the cell.

I don’t know what is the most efficient way to do that.
With a LINQ query to check?
With a for each row and convert each col into Int32 and then, add each column?

Thank you

Hi @Vandekamp

Try this in Assign activity:

dt.Columns("RESULT").Expression = "[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]"

Hope it helps!!

@Vandekamp
Assume the column name is ‘Result Checking’.
In your for each row of dt_Annual, use assign activity.

row(“Result Checking”) = If((CInt(row(“Jan”)) + CInt(row(“Feb”)) + CInt(row(“Mar”)) + CInt(row(“Apr”)) + CInt(row(“May”)) + CInt(row(“Jun”)) + CInt(row(“Jul”)) + CInt(row(“Aug”)) + CInt(row(“Sep”)) + CInt(row(“Oct”)) + CInt(row(“Nov”)) + CInt(row(“Dec”))) = CInt(row(“RESULT”), “Match”, “Not match”)

Then you may update the dt_Annual to your excel.

Hi @Vandekamp

1. Read Range (Excel file) --> dt_Annual

2. Build DataTable (dt_Result)
   - Add Data Column: "RESULT"

3. For Each Row (in dt_Annual)
   - Assign: sum = row.ItemArray.Skip(1).Take(12).Sum(Function(x) Convert.ToInt32(x))
   - If: Convert.ToInt32(row("RESULT")) <> sum
     - Assign: row("RESULT") = sum
     - Add Data Row: {sum} to dt_Result

4. Write Range (Excel file) --> dt_Annual

5. If dt_Result.Rows.Count > 0
   - Write Range (Excel file) --> dt_Result

Hope it helps!!

Thank you both,
I will try with a For Each Row in all the dt and Convert to Cdbl (could be decimal) with Index column, not column name, because it could change. If the sum is not equal, I update the column in an assign

CDbl(CurrentRow(13)) = Cdbl(CurrentRow(1))+Cdbl(CurrentRow(2))+Cdbl(CurrentRow(3))+Cdbl(CurrentRow(4))+Cdbl(CurrentRow(5))+Cdbl(CurrentRow(6))+Cdbl(CurrentRow(7))+Cdbl(CurrentRow(8))+Cdbl(CurrentRow(9))+Cdbl(CurrentRow(10))+Cdbl(CurrentRow(11))+Cdbl(CurrentRow(12))

Hi @Vandekamp

→ Build Data Table
image
Output-> dt_Result
→ Read Range Workbook


Output-> dt_Annual
→ Use below code in Invoke Code:

' Create new DataTable dt_Result
dt_Result= New DataTable()
dt_Result.Columns.Add("RESULT", GetType(Integer))

' Iterate through each row in dt_Annual
For Each row As DataRow In dt_Annual.Rows
    Dim sum As Integer = row.ItemArray.Skip(1).Take(12).Sum(Function(cell) If(TypeOf cell Is DBNull, 0, Convert.ToInt32(cell)))
    Dim resultValue As Integer = Convert.ToInt32(row("RESULT"))
    If sum <> resultValue Then
        ' Add row to dt_Result
        dt_Result.Rows.Add(sum)
		Else
			dt_Result.Rows.Add(resultValue)
    End If
Next

Invoked Arguments


→ Write Range Workbook dt_Result
image

Sequence17.xaml (9.5 KB)

Hope it helps!!