Check Excel to contain duplicate row and paste in other excel

Hi,

I have a Excel following format as Input

Name City
A Kolkata
B Pune
C Delhi
D Pune
E Kolkata

Output Values should be

Name City
A Kolkata
B Pune
D Pune
E Kolkata

code should not be hardcoded
can anyone help?

Thanks

Hi @ch460960

Use Assign Activity

LHS → Create an Data Table Called DtDuplicate

RHS → Write the expression

(From p in DT.Select() where( From q in DT.Select() where q("City").Equals(p("City")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Use Write Range activity and pass the DtDuplicate in the Dt

Regards
Gokul

Hi @ch460960 ,

Assuming that you only want a table of duplicate values, here is a code that ought to do the trick →

Dt.AsEnumerable().GroupBy(Function(g) g("City").ToString.Trim).Where(Function(w) w.Count()>1).SelectMany(Function(sm) sm).CopyToDataTable() 

This ofcourse, has to be outputted to another table. You can clone the first datatable like so →

Dt_dups = Dt.Clone()

Kind Regards,
Ashwin A.K

1 Like

what’s the variable type DtDuplicate?

Create an variable → DtDuplicate → Variable Type → Data Table @ch460960

Regards
Gokul

it is thowing error

Error ERROR Validation Error Compiler error(s) encountered processing expression “(From p in dt.Select() where( From q in dt.Select() where q(“City”).Equals(p(“City”)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()”.
‘CopyToDataTable’ is not a member of ‘System.Array’. Main.xaml

@ch460960

we assume that you are also interested to keep the origin order

Assign Activity
LHS: dtResult | DataType: DataTable
RHS

(From d in dtData.AsEnumerable
Group d by k=d("City").toString.Trim into grp=Group
Where grp.Count > 1
From g in grp
Order by dtData.Rows.IndexOf(g)
Select r=g).CopyToDataTable
1 Like

Hi @ch460960

Have a look on the XAML file

GetDuplicate.xaml (5.5 KB)

Regards
Gokul

1 Like

Thanks . Both of them are working @ppr @Gokul001

Perfect

Just have a final look on the 3 different approaches

Gokul’s Approach:

  • it is reflecting a nested loop where in inner loop all rows are checked
  • when more then 1 rows are found within the where block then the where check is matching
  • current loop rows will be forwarded
  • with the nested loop approach we have n rows * n rows (when not reduced by the where) touches

Ashwins Approach:

  • Group By within Method syntax

ppr Approach

  • group by within Query Syntax + plus Ordering on the origin datarow position

For entering more the LINQ topics have a look here:
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

Great Document @ppr

is there any .net function available to check duplicates from every row and write data to excel?

lets scope topics on only 1 case. This is making more easy for researchrs to find solutions. Just open a new topic on this case

we would recommend to be very specific on what is needed.

  • Detecting on all columns or column set
  • removing / retrieving all duplicates
  • removing / retrieving 1 row from the duplicates by a dedicated strategy e.g. first()

So just put all the details also in you next topic question.

Short answer: Depending on the details, .net is giving us options to implement it

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