Assuming, we have an excel sheet with two columns, Client ID and Client expenditure. What I am trying to do is have robot access the spreadsheet and perform the following:
*Search for duplicate client id
*If a duplicate is found, then sum the overall client expenses
Excel Application scope with read range to read the excel
Assign activity to assign dtTemp.Select(“ClientID= ‘yourVariable’”) to arrayofDaraRow variable
check if count is greater than 1
sum the expense column using the given code dtTemp.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(1).ToString.Trim) ).ToString here I have converted the input to double format, you can use integer format if there is no decimals present and I have stored in a string variable.
Please let us know if this was your solution.
Regards,
Pavan H.
You can try with (From r In dtData.AsEnumerable() Select C1 = r.Field(Of String)(“ClientID”), C2 = r.Field(Of Decimal)(“ClientExpenditure”) Group By C1 Into Group Select C1, C2 = Group.Sum(Function(x) x.C2)).ToArray()
@Sob
For detecing the relevant values (Col ID, Col Key) of the duplicates I would suggest for:
Using an assign activity
Statement:
(From r In dtSample.AsEnumerable()
Select C1 = r(0).ToString.Trim, C2 = r(2).ToString.Trim
Group By C1, C2 Into Group
Select C1, C2, Count = Group.Count
Where Count > 1
Select New String() {C1,C2}).ToList
returns a List of String()
The second part could be done in a classical way within a for each acitivty, taking care about the summing up (refer to some suggestions on above)
In case you need further help, please open a new Topic as your scenario is valuable for others and schould not get lost on the origin one
If the datatable contains null value then it’s not working. What will be the query in that case?
I need to find duplicate in a column (eg branch account) and add the duplicates values in corresponding columns which may or may not contain null values. I am able to get all duplicate values bt sum is nt wrkng. Plz note the values to be added are decimal.
Plz suggest.