i need to create a sequence that compares the totals in sheet1 and sheet2… If the totals are matched, i will send an email to the designated user with attaching the report. On the other hand, if they’re unmatched, highlight the cells of specified affected months and notify the user about the invalid totals with attaching the report.
The number of rows in sheet2 is not static, meaning that the sequence should work even when the number of rows changes.
i firstly want to compare each month with the corresponding month on the other sheet and check the match… thanks in advance
This is one way: You have to perform a few DataTable based operations after reading the worksheets in.
Then there may be fixed conventions that you might have to adopt.
For Sheet 2:
In Sheet 2 (lower in your picture), I would make the following fixed assumptions:
The Read begins from A4 and not A1. So my read range input would be A4 and not A1
If you read this range into DataTable2 then all your totals are on the last Row which corresponds to: DataTable2.Rows.Count or DataTable2.Rows.Count-1
based on whether or not you are enabling AddHeader option when Reading the worksheet.
rowNum = 4+DataTable.Rows.Count or 4+DataTable.Rows.Count-1
Again, depending on the header, and 4 is the starting row of Sheet 2
Therefore the start range of the Totals Row you have to Read is "B"+rowNum.ToString() through "M"+rowNum.ToString()
Obviously “N” in this case is again variable, but over a year there are only 12 months so an additional logic can help you figure that out based on the current month. Starting from Column B4 you will read on 12 columns regardless of the what the start and end months are on this Sheet
For Sheet 1:
I presume that the Totals column is fixed in which case you would read this row into another DataTable. The start range for this would be A6.
Now that you have both DataTables in memory, each containing one row all totals. You can do a DataTable compare on the Totals to arrive at your decision logic. By this time you know what the Rows and cells you are comparing.
Example in this case it's Row 6 on Sheet1 and Row 25, Cells B25 through M25 (per the math above) on Sheet 2.
You must be able to use that in the Highlight Cell activity to mark that cell if any of the totals don’t match
Well you must be able to solve that with a left or right outer join of the two datable rows. But given that the number of months you have to check for changes from Month 1 to Month 12 each month, writing a join statement between the two Data Tables might be tricky.
Instead I would loop through each column of DT1 (pulled from Sheet 1) and match each value from column 1 through Column N with its corresponding column value from DT2 (sourced from Sheet 2)
For example, the code may look something like this:
'Initialize column counter
'Initialize Unmatched counter
X=0
Unmatched=0
For each c1 from DT1.Columns
V1 = value of c1
V2 = DT2.Columns(X)
If V1 <> V2 then
'write logic to convert X to the cell name
'For instance if values in Col1 are not matching then you have to highlight column A6 in Sheet 1 and B25 in Sheet 2.
Cell1= "A"+(2+X).To string()
Cell2= "B"+(25+X).To string()
' Use Highlight Cell Activity to highlight Cell 1 in ExcelAppScope1
'Use Highlight Cell Activity to highlight Cell 2 in Excel AppScope2
Unmatched=Unmatched+1
End If
X=X+1
End For Each
If Unmatched>0 then
Send email attaching highlighted reports
End if
Note that the For Each Automatically controls the number of columns it has to navigate and with each month it automatically increases from Month 1 (X=0) and Month 12 (X=11)
The cells to highlight will automatically change with the X value added to their start positions in each sheet. The number of times X increments to will be limited by the For Each iterations.
