Identifying Duplicates in a excel column

[Ritika_Singh]

Hi, I have an excel sheet with few columns and one particular column has duplicate values including blanks as well, i want to separate out the unique and the duplicate values in two different datatables , right now I am using the below query , but not able to get the desired result ,
unique records : DT.DefaultView.ToTable(True,“Ticket ID”,“Assignee name”,“Policy Number”,“Ticket subject”,“Submitter name”, “Submitter email”,“Requester Type”,“Profit Center”,“Ticket created - Date”,“Tickets”)

duplicate records : (From p inDT.Select() where (From q in DT.Select() where q(“Policy Number”).Equals(p(“Policy Number”)) Select q).toArray.count>1 select p).ToArray.CopyToDatatable()

Requesting for your suggestions.

[Ritika_Singh]

@Yoichi any suggestions from your end would be of great help

[Gokul001 (Gokul Balaji)]

Hi @Ritika_Singh

Try with this expression to get the Unique value

(From d In DtRead.AsEnumerable
Group d By k=d("Column Name").toString.Trim Into grp=Group
Where grp.Count = 1
From g In grp
Order By DtClone.Rows.IndexOf(g)
Select r=g).CopyToDataTable

Try with this expression to get the Duplicate value

(From d In DtRead.AsEnumerable
Group d By k=d("Column name").toString.Trim Into grp=Group
Where grp.Count > 1
From g In grp
Order By DtClone.Rows.IndexOf(g)
Select r=g).CopyToDataTable

[Gokul001 (Gokul Balaji)]

Hi @Ritika_Singh

Another approach

How about this expression

DtDuplicate = (From p in DT.Select() where( From q in DT.Select() where q("Column Name").Equals(p("Column Name")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

DtUnique = (From p in DT.Select() where( From q in DT.Select() where q("Column Name").Equals(p("Column Name")) Select q).ToArray.Count=1 Select p).ToArray.CopyToDataTable()

Check out the XAML file

GetDuplicate.xaml (8.0 KB)

[Ritika_Singh]

@Gokul001

(From d In DtRead.AsEnumerable
Group d By k=d("Column Name").toString.Trim Into grp=Group
Where grp.Count = 1
From g In grp
Order By DtClone.Rows.IndexOf(g)
Select r=g).CopyToDataTable

What is DTClone and DTRead

[Gokul001 (Gokul Balaji)]

Hi @Ritika_Singh

DTRead is the Input excel file where we have the source data.

DTClone the structure of the DataTable, including all DataTable schemas and constraints.

Reference: DataTable.Clone Method (System.Data) | Microsoft Learn

In the image instead of DTClone in hvae DtOutput

Regards
Gokul

[Ritika_Singh]

@Gokul001

(From d In DtRead.AsEnumerable
Group d By k=d("Column name").toString.Trim Into grp=Group
Where grp.Count > 1
From g In grp
Order By DtClone.Rows.IndexOf(g)
Select r=g).CopyToDataTable

I used the same DTRead table in both the places it worked…