Filter/Select Non Duplicate Values

Hi

I am having the excel sheet with the following values, I need to filter the non-duplicate values or delete all duplicates.

Input:
image
Ouput Expected :
image

1 Like

Have you tried using Remove duplicates range activity @kuppu_samy?

1 Like

But it would retain one duplicate from it right

2 Likes

No, It will remove the duplicate rows in the range we mentioned in the properties of the activity, and let the data remains same as it is.

1 Like

Yes, I tried but it return 1,2,3,4,5,6 values. I require 1,2,3,5,6.

Yeah got it :smiley:. I just understand the question wrong. @vishal.kp Sorry mate, You are right :slight_smile:

3 Likes

Yes, @HareeshMR @vishal.kp. you’re correct!! :grinning:. What is need to achieve this?

@Palaniyappan @lakshman @anoopap kindly review this.

Kindly try with this expression
If the datatable name is dt
dt = (From r In dt
Group By Distinct = New With {Key.Numbers = CStr(r(“Numbers”))} Into Group
Where Group.Count = 1
Select Distinct).CopyToDatatable()

Cheers @kuppu_samy

Couldn’t covert into DataTable, there is no option for CopyToDatatable().

1 Like

@kuppu_samy
The Task can be solved with Help of Set operations. Identify in the First Run the duplicates and Then removed IT with the use of an except Statement. Just Let US know If you are dealing only with one column (so IT can be done in list Base) or If multi column rows are to handle

kindly have a view on this and add to the xaml by opening it in notepad

Cheers @kuppu_samy

1 Like

Hi @kuppu_samy

Please try the below code

DT = DT.DefaultView.ToTable(True, "Numbers")

Here DT is the table that you want to filter and I’m assigning the filtered value to the same datatable.

If you want to filter using more columns

Then try this

DT =  DT.DefaultView.ToTable(True, "column1","column2","column3")

Hope this will help

1 Like

@kuppu_samy
Below a more detailled description on how to do (1 column, List approach)


Build Datatable - just simulating Excel readin, giving a Datatable
1. Assign: creating a string list from the Numbers column
dtData.AsEnumerable.Select(Function (row) row(“Numbers”).toString.Trim).toList
2. Assign: retrieving the duplicates:
(From s In Numbers
Group s By no=s Into Group
Select no, count=Group.Count
Where count>1
Select no).ToList
3. Assign filtering out the dplicates from the Numbers
Numbers.Except(Duplicates).toList

Find XAML here: kuppu_samy.xaml (9.1 KB)

Kindly note: same approach with Except can also be shifted to Datatable base with e.g. more columns. If help is needed on that just ping us. Let us know your feedback

2 Likes

@kuppu_samy
In Case of you need the result in a Form of Datatable so you can Clone the Origin Datatable with the one Numbers column to a Second empty one with the datatablevar.clone Statement.
Finally you can iterate over the nonduplicates list and add each entry with an add row Activity to the Second Datatable.

Let me know in Case of Open questions

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