Support for linquery or VB code

[manoj2500 (robo)]

Hi,

i want to do is i want read an excel where data is there then i want to read column D (column D might have same value multiple times ) i want then check all value that are there in E column against the D column value do a total of count populate that count in column F

EX: value at D2 is Q78259 and at E2 is 102 again at D7 and E7 is Q78259 and 102 then at F2 it should populate 204 and same way for E7 as well 204
input.xlsx (8.5 KB)
outputdata.xlsx (8.5 KB)

[Anil_G (Anil Gorthi)]

@manoj2500

use a write cell activity on cell F2 with values as "=SUMIF(D2:D7,D2,E2:E7)"

then use auto fill range to fill the formula in all rows

output would be like below

No linq or anything is needed..

cheers

[manoj2500 (robo)]

@Anil_G but this will take lot of time if records are more like any other method that will help as if i can use =SUMIF(D:D,D2,E:E) but this will take lot of execution time when we have 1000 or 10000 records

[Anil_G (Anil Gorthi)]

@manoj2500

it would not ..hardly it might take few seconds

but if you still need a formula only and want to write back

use this

first read data and then add a new column to that datatable then use invoke code with dt as in/out argument and pass the read data..also send same datatable as indt with only in type of argument

dt.AsEnumerable.ToList.ForEach(Sub(x) x("LastColumnName")=indt.AsEnumerable.Where(function(p) p("User").ToString.Equals(x("User").ToString)).Sum(function(p) CDBL(p("Count").ToString)).ToString)

then write back the dt to excel again

cheers

[MohammedShabbir (shabbir.qs)]

Hi @manoj2500 ,

You can use this code in invoke code.

’ Get column names dynamically
Dim colD As String = dt.Columns(3).ColumnName ’ Column D
Dim colE As String = dt.Columns(4).ColumnName ’ Column E

’ Create Column F if missing
Dim colF As String
If dt.Columns.Count >= 6 Then
colF = dt.Columns(5).ColumnName
Else
colF = “TotalValue”
dt.Columns.Add(colF, GetType(Double))
End If

’ Dictionary to store totals
Dim totals As New System.Collections.Generic.Dictionary(Of String, Double)()

’ First pass: calculate totals for each key in Column D
For Each r As System.Data.DataRow In dt.Rows

Dim key As String = ""
If Not IsDBNull(r(colD)) Then
    key = r(colD).ToString().Trim()
End If

Dim amount As Double = 0
If Not IsDBNull(r(colE)) Then
    System.Double.TryParse(r(colE).ToString(), amount)
End If

If totals.ContainsKey(key) Then
    totals(key) = totals(key) + amount
Else
    totals.Add(key, amount)
End If

Next

’ Second pass: write totals into Column F
For Each r As System.Data.DataRow In dt.Rows

Dim key As String = ""
If Not IsDBNull(r(colD)) Then
    key = r(colD).ToString().Trim()
End If

If totals.ContainsKey(key) Then
    r(colF) = totals(key)
Else
    r(colF) = 0
End If

Next

Sequence1.xaml
(13.1 KB)

If helpful, mark as solution. Happy automation with UiPath

[Alex17pat]

Nice thread! The LINQ/VB snippet shared here actually helped me clean up a similar workflow. Didn’t know UiPath handled it this way.