Hi Guys
I am trying to remove the duplicates rows when one column doesnt match.
and the unique value needs to be in same sheet…we cant write it on another sheet.
in general it can be handled as a groupby case
Assign Activity:
dtCleansed | Datatype: DataTable =
(From d In dtData.AsEnumerable
Group d By k1=d("PO").toString.Trim, k2=d("item no").toString.Trim Into grp=Group
Let gpo =grp.OrderBy(Function (g) Convert.ToInt32(String.IsNullOrEmpty(g("status").toString.Trim)))
Select r = gpo.First()).CopyToDataTable
2 Likes
This can be done by reading the sheet into a datatable, for example dtYourSheet.
Create a new datatable, for example dtNoDuplicates.
For each row of datatable dtYourSheet
If dtNoDuplicates.RowCount = 0
Add datarow dtNoDuplicates CurrentRow
Else
Lookup dtNoDuplicates to check if CurrentRow(“PO”) is existing. If it exists, check if dtNoDuplicates(“status”).ToString = “” if it is, update it with the current status.
If not existing add currentrow to dtNoDuplicates
After the for each row, write the dataable to your excel sheet (would be good to clear the sheet first as the new datatable is expected to be shorter than the original table in the sheet)
Also why does P29093 is still duplicated in your “after”?
Hi @ahmed4566
You can use the Linq to get the result
dt_Output.AsEnumerable() _
.GroupBy(Function(i) New With { Key .PO = i.Field(Of String)("PO"), Key .ItemNo = i.Field(Of String)("item no") }) _
.Select(Function(g) g.OrderBy(Function(row) If(String.IsNullOrWhiteSpace(row.Field(Of String)("status")), 1, 0)).First) _
.CopyToDataTable
Hope this helps 
correct It shouldnt be there …its just a sample to work on real data
I will try this and keep u updated
I will try this and keep u updated
if you can create a workflow …that would be great help
podata.xlsx (9.4 KB)
its working absolutly fine …thanks but I need to write on the same sheet/
how is it possible bro?
keep in mind, when writing it back to the same sheet, that old data is not deleted (as we expect less result rows, as initial input rows)
- delete sheet / clear sheet
- then write the result back
did the exact thing and it started working …thank Peter
Perfect so the topic can be closed by marking the solving post
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.