Group Rows based on Columns and Identify the groups with more than one distinct values in a column of the group

Help
, ,
#1

Hello Mates, I have an issue with identifying duplicates in a table based on the two or three columns and keep the ones which have more than one distinct values in a column of a group.

For say we have a Table as below:

Initial :
Capture1

Task is to get the Duplicates based on Name and Place Columns.
image

Now we have the keep the groups which have more that one distinct values in the company column of each group…
image

Prem Houston combination group belongs to both 123 and 456 companies, we have to keep them.

I am able to get the duplicates by using a linq query

(From d In Table
Group d By C1 =d(“Name”).ToString.Trim, C2=d(“Place”).ToString.Trim Into grp=Group
**Where grp.count > 1 **
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

I am unable to add another condition counting the uniques values of the group.

(From d In Table
Group d By C1 =d(“Name”).ToString.Trim, C2=d(“Place”).ToString.Trim Into grp=Group
Where grp.count > 1 And grp.Company.Rows.Distinct.count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

The above does not work… Kindly help me resolve the issue…
Thank you in advance…

#2

@pottamsaiprem
you was close to solve it. the part grp.Company.Rows is the blocker as the columns cannot be referenced with a dot syntax.

give a try on:

(From d In Table
Group d By C1 =d(“Name”).ToString.Trim, C2=d(“Place”).ToString.Trim Into grp=Group
Where grp.count > 1
Where grp.Select(Function (x) x(“Company”).toString.Trim).Distinct().Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

Kindly note the where was split on two lines just for a more easy to read. Feel free to combine it with an AND.

Let us know your feedback

#3

@ppr
Thank you so much for your help… This works perfect.
Is there a way we can pass on the Column names Dynamically in the form of a variable ? The User might choose 1,2, or 3 columns based on the data .

#4

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