Remove duplicates in two columns and keep rest of all the column values

[prabhu_ponnusamy]

Hi all,

need to remove duplicates in two columns (Customer Code,Jurisdiction Name) the attached sheet and also keep rest of all the column values.

if I use dt_Inputsheet.DefaultView.ToTable(True,“Customer Code”,“Jurisdiction Name”) syntax I am getting these two columns alone in the output dt . but i need rest of all the columns and corresponding values.

[Yoichi]

Hi,

Perhaps you should use Linq - GroupBY method. The following topic might help you.

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

If you can share your excel file, we might write expression for it.

Regards,

[prabhu_ponnusamy]

Sample.xlsx (6.8 MB)

remove duplicates for “Customer Code”, “Jurisdiction Name” columns and keep all the columns.

[Yoichi]

Hi,

How about the following sample?

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Customer Code").ToString,r("Jurisdiction Name").ToString)).Select(Function(g) g.First).CopyToDataTable

Sample20210827-1.zip (2.3 KB)

Note: More specifically, you need to decide which duplicated rows to keep.

Regards,

[Jeroen]

@Yoichi nice solution! I’ve been seeing a lot of these Linq statements in the forum, and I’d like to learn more about it. Is there a resource you can recommend?

[Yoichi]

Hi @Jeroen ,

Unfortunately, I’m not very familiar with good LINQ resources in English because my native language is non-English (Japanese). Now @ppr is working on making documents for LINQ (as the following, for example), and it will help us better understanding LINQ, I think.

[HowTo] - First Start with LINQ (VB.Net)

LINQ is part of the .NET framework and offers capabilities for interacting with data, objects or other sources within a particular syntax. This HowTo introduces on starting with LINQ (Language-Integrated Query). Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Typical tasks for implementing parts of an automation project are interacting with e.g. with data in a datatable or items from collections. LINQ can help to implement it into a compact and standa…

Regards,

[ppr (Peter Preuss)]

Answered with alternate on the forked / duplicated topic thread:

Remove duplicates in DT for specific columns and keep all column and values

Hi all, need to remove duplicates in two columns (Customer Code,Jurisdiction Name) the attached sheet and also keep rest of all the column values. if I use dt_Inputsheet.DefaultView.ToTable(True,“Customer Code”,“Jurisdiction Name”) syntax I am getting these two columns alone in the output dt . but i need rest of all the columns and corresponding values. [image] Sample.xlsx (6.8 MB)