How to Remove duplicate rows in columns and delete it

[suraj_gaikwad]

Hello team,

In the scenario I have to delete specific row but there is an condition like column name is (customer names) are and there is an column name is {Match Type} But there value is fixed ( Probable, Confirmed) so I have to remove those rows like

Which all Match types are probably only so in below example suraj name that dats should be delete only Vikram data I need bcz Vikram data are all probable and in suraj there is on confirmed so I want to delete that data

Cust name match type
1)Suraj probable
2)Suraj. Probable
3)Suraj Confirmed
4)Suraj. Probable
5)Vikram Probable
6)Vikram Probable

New R508_15-10-2023-15-42-54-256.xlsx (19.2 KB)

Thanks

[Dilli_Reddy]

@suraj_gaikwad

Excel_dt.AsEnumerable.GroupBy(Function(a) Tuple.Create(a(“Column Name1”).ToString,a(“Column Name2”).ToString)).Select(Function(b) b.First).CopyToDataTable

[sai_gupta]

@suraj_gaikwad

YourDataTableVar = YourDataTableVar.Select("NOT([Match Type] = 'Confirmed')").CopyToDataTable

cheers…!

[Parvathy (PS Parvathy)]

Hi @suraj_gaikwad
Can you specify the requirement what you want to delete.

Assign activity:
dt = dt.AsEnumerable().Where(Function(row) row.Field(Of String)("Match Type") = "Probable").CopyToDataTable()

This LINQ query filters the rows in the DataTable dt to include only those with a “Match Type” equal to “Probable” and creates a new DataTable with the filtered rows. The original DataTable will now contain only the rows that match your criteria.

Hope it helps!!
Regards,

[suraj_gaikwad]

I have to remove all confirmed case but there is scenario for example 10 cases is there those 10 cases are probable but one case is confirmed so I want to delete those all 10 rows.

2 condition of the all 10 cases is probable that data I want and it’s true condition

@Parvathy

Thanks

[suraj_gaikwad]

I tried it’s deleting only confirmed cases that is not in requirement

In above image i marked the case (Manoj agrawal) his one case is confirmed so delete those all ( Manoj agrawal) cases

If any case is found confirmed delete that all same name of rows

@sai_gupta @Dilli_Reddy @Parvathy

Thanks

[sai_gupta]

@suraj_gaikwad

try this:

YourDataTable = YourDataTable.AsEnumerable().Where(Function(row) row("Name").ToString() <> "Manoj Agrawal").CopyToDataTable()

cheers…1

[Dilli_Reddy]

try this

DT = DT.AsEnumerable().Where(Function(row) row(“Manoj Agrawal”).ToString() <> “Name”).CopyToDataTable()

[suraj_gaikwad]

It’s not working data is still there as it is,

I don’t want to delete only single there is the multiple data on regular basis

@Dilli_Reddy @sai_gupta

[sai_gupta]

@suraj_gaikwad

try this:

YourDataTable.AsEnumerable().Where(Function(row) row("Name").ToString() = "Manoj Agrawal" And row("Status").ToString() = "Confirmed").ToList().ForEach(Sub(row) YourDataTable.Rows.Remove(row))

cheers…!

[Dilli_Reddy]

Try this

1. Use the Read Range activity to read the Excel file into a DataTable.
2. Create a Filtered DataTable:

• Use a For Each Row activity to loop through each row in the DataTable.
• Within the loop, create a variable to store the “Cust name” value of the current row.
• Create a Boolean variable to keep track of whether you should remove the current row.
• Create another loop to iterate through all rows with the same “Cust name” as the current row.
• Inside this second loop, check if the “Match Type” of each row with the same “Cust name” is “Confirmed.” If you find any “Confirmed” Match Type, set removeRow to True.
• After the second loop, check the removeRow variable. If it’s True, use the Remove Data Row activity to remove the current row from the DataTable.

[Shiva_Nikhil]

@suraj_gaikwad

try this

dt.AsEnumerable.GroupBy(Function(r) r(1).ToString).where(function(grp) not grp.Any(function(a) a(“Match Type”).ToString.trim.Equals(“Confirmed”))).Select(function(g) g.copytodatatable).toarray

datatype is array of datatable

use for each activity to get all the datatables

hope this helps

[suraj_gaikwad]

I have shared the excel sheet where the scenario is there

@Shiva_Nikhil @Dilli_Reddy @Parvathy @sai_gupta

Thanks

[Shiva_Nikhil]

@suraj_gaikwad

NewBlankTask.zip (54.2 KB)

refer this

[ppr (Peter)]

Assign Activity
dtResult | DataTable =

(From d in YourOriginDataTableVar.asEnumerable
Group d by k("Customer Name").toString.ToUpper.Trim into grp=Group
Let chk = grp.any(Function (g) g("Match Type").toString.ToUpper.Trim.Equals("CONFIRMED"))
Where Not chk
From gm in grp
Select r = gm).CopyToDataTable

Handling empty results we can do:
[FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

[suraj_gaikwad]

@Shiva_Nikhil

It’s working but in if condition some data going to else and other then?

[Shiva_Nikhil]

@suraj_gaikwad

what i made is like ,the first one will go to then and rest will append to it

[Siva_krishna_Thumma]

Hi @suraj_gaikwad,

Please check below approach and let me know if it is helpful.

1. Read Excel / Csv and store data into datatable
2. Convert all the data into one format
   
   
   
   3)Group by Customer name (dt.AsEnumerable().GroupBy(function(x) x(“Customer Name”)).ToList())
3. Iterate GroupBy result and check condition as below
4. item.CopyToDataTable.AsEnumerable().All(function(x) x(1).ToString.ToUpper.Contains(“PROBABLE”))
5. if condition ok, merge datatable into final datatable

8. flow
   
   

[suraj_gaikwad]

@Shiva_Nikhil

Because I’m trying with all data are probable but it’s taking first data in then part other is else

So how I can change this part

[Shiva_Nikhil]

@suraj_gaikwad

no problem you will get the modified data at last

try with your excel and let me know it works or not