Excel , data

Help Process Mining
1

i have an Excel sheet that contains product codes, dates, and quantities, where some product codes appear more than once. I want to create a new table in which each product code appears only once, keeping the oldest date associated with that code and the corresponding quantity. If the same product code appears multiple times with the exact same date, the quantities should be summed and the total quantity should be stored. If the same product code appears with a more recent date compared to the oldest one, that record should be ignored. I have been trying to achieve this in different ways for the past two days without success. Can someone help me? thank you

Forum Engagement Daily Reports
2

@nkaizer

Share some sample input and output. Also what approach you tried and the issue you are facing.

3

You can do it with ease in UiPath using linq.

Can you share a sample or some screenshot’s, so that we can have a clarity.

4

I tried using Read Range to get the Excel table, then a For Each Row loop to iterate through all rows. Inside the loop, I used If activities to check valid dates and whether the code already exists in a dictionary. I used assign activities to store the oldest date and update quantities if the same date appears, and Add Data Row to populate the result DataTable. Finally, I wrote the output with Write Range. Despite this, the results were not correct.

5
Codes Qty Date
42230298 22 11/27/2025
33230288 3 12/12/2025
22230304 204 3/31/2026
82230297 11 3/31/2026
82230297 464 3/31/2026
72230299 94 5/28/2026
22290285 132 6/5/2026
24230287 31 6/5/2026
33230288 50 6/5/2026
22230280 159 6/11/2026
42230298 320 7/7/2026
6

My result looks like this, but it is incorrect (the dates are wrong and the codes are repeated multiple times):
01/01/01-132
01/01/01-159
01/01/01-132
01/01/01-159
01/01/01-31

7

Try this,

  1. Read Range
    Read Excel into dtInput
  2. Assign (LINQ only, no Rows.Add inside it)
    Create a new DataTable from LINQ properly.

Assign → dtResult = (
From r In dtInput.AsEnumerable()
Group r By code = r(“Codes”).ToString() Into grp = Group
Let minDate = grp.Min(Function(x) CDate(x(“Date”)))
Select New Object() {
code,
grp.Where(Function(x) CDate(x(“Date”)) = minDate).
Sum(Function(x) CInt(x(“Qty”))),
minDate
}
).ToList().
Aggregate(
dtInput.Clone(),
Function(dt, x)
dt.Rows.Add(x(0), x(1), x(2))
Return dt
End Function
)
3. Write Range
Write dtResult once to Excel

8

i changed a bit the process because this for me doesn’t work . but i use the logic which was correct. thank you!!!

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.