How to Separate the Unique and Multiple Line in the Column2 Using LINQ

Help Activities
, , ,
1

Hi,

Need to separate the Input file In to two different sheets using LINQ, Based on Column 1 and Column 2

Sample Input and Output Structure

Input.xlsx (10.8 KB)

image

2

HI,

Can you try the following sample?

image
image943×789 34.7 KB 

dtUnique = dt.AsEnumerable.GroupBy(Function(r) r("Column 1").ToString).Where(Function(g) g.GroupBy(Function(r) r("Column 2")).Count=1).SelectMany(Function(g) g).CopyToDataTable

dtMultiple = dt.AsEnumerable.GroupBy(Function(r) r("Column 1").ToString).Where(Function(g) g.GroupBy(Function(r) r("Column 2")).Count>1).SelectMany(Function(g) g).CopyToDataTable

Sample20230210-7L.zip (10.6 KB)

Regards,

1 Like
3

Thanks for the Solution @Yoichi

And i have one more doubt

Need to separate the Input Based on different sheets. Based on Column 1 and Type

Input and Output

Input.xlsx (11.4 KB)

image
image511×539 11.2 KB

4

Hi,

How about the following?

image
image953×737 35.3 KB 

dict = dt.AsEnumerable.GroupBy(Function(r) r("Column 1").ToString).Select(Function(g) Tuple.Create(String.Join("",g.Select(Function(r) r("Type").ToString).OrderBy(Function(s) s).Distinct),g)).GroupBy(Function(t) t.item1).ToDictionary(Function(g) g.Key,Function(g) g.SelectMany(Function(g2) g2.item2.Select(Function(r) r)).CopyToDatatable)

Sample20230210-7Lv2.zip (20.9 KB)

Regards,

1 Like
5

Can you please explain this expression? @Yoichi

6

Can you please explain this expression?

dt.AsEnumerable.GroupBy(Function(r) r("Column 1").ToString)

The above create IEnumerable<IGrouping> grouping by value of Column1

.Select(Function(g) Tuple.Create(String.Join("",g.Select(Function(r) r("Type").ToString).OrderBy(Function(s) s).Distinct),g))

In each group, create tuple which has value of Type column which is sort ascending and distinct such as “A” ,“AB” etc. and IEnumerable<IGrouping>

.GroupBy(Function(t) t.item1)

Grouping by modified value of Type.

.ToDictionary(
Function(g) g.Key,
Function(g) g.SelectMany(Function(g2) g2.item2.Select(Function(r) r)).CopyToDatatable)

Finally, create dictionary which key is modified value of Type (such as “A”,“AB”) and value is DataTable converted from IEnumerable<IGrouping>

The above can handle any string such as “C” or “D” in Type column.

Regards,

1 Like
7

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