How to find and get duplicate values in Excel?

Help Activities
, ,
1

Hi everyone!

I have excel which has 60k rows. I have to find one column’s duplicate values and get them another excel sheet and send it with email.

I use this way. But it’s too slow, it takes about an hour.

How can I do it another way?

image
image1007×545 24.2 KB

I send example excel, too.

excel.xlsx (896.9 KB)

2

Hi @ozgecatak ,

Do you want to Get Duplicate values from a Column and place them in another Excel Sheet and also update the Comment column for those rows which are duplicates ?

Could you also provide us with the Expected Output for the Input data provided ?

3

You can try with LINQ, should be quite quicker:

var_dt.AsEnumerable.GroupBy(function(x) x("Fatura No")).Where(function(y) y.Count>1).

This applied to your excel returns 12 values in 1 second

Edit: Attaching an example workflow, and the results:

image
image1217×86 3.16 KB

FindDuplicates.xaml (5.5 KB)

Edit#2: Attaching aswell the results from finding duplicates using Excel’s functionality: Same results (24 results in excel, since there are 2 of each)

image
image234×515 4.7 KB

4

Hi, thanks for answering.

How to write duplicate records in excel sheet?
I use your linq query.

image

How do I write ?

5

HI @ozgecatak

How about this expression?

Use Assign activity DtOutput | Variable type | DataTable

DtOutput  = (From p in DT.Select() where( From q in DT.Select() where q("Fatura No").Equals(p("Fatura No")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Use Write Range activity and pass the DataTable as DtOutput

Regards
Gokul

6

image
image717×442 19.5 KB

@ozgecatak

7

Have you got the Output @ozgecatak

8

To keep it simple, since LINQ would not add a huge amount of improvement, as it did with the 1st one, you could simply iterate through the result (Array of Strings) and use the activity to “Add Data Row”:

1- Assign with the original LINQ + .ToArray

var_dt.AsEnumerable.GroupBy(function(x) x("Fatura No")).Where(function(y) y.Count>1).ToArray

1- Create sample DataTable either by using originaldt.Clone, or Build Datatable, as you prefer
2- For Each string in Resulting Array
3- Add Data Row with the current string
4- ReWrite to excel the resulting DT

Example:

image
image836×761 25.2 KB

9

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