Split excel if the column B-C-D is same as other row's column B-C-D

Help Community
, , ,
1

Hi Everyone!

I’ve a problem. Column A have unique values. I’m trying to split this excel and I want to split by this condition: if column B and column C and column D is same then I can split row 1 and row 2 to separate file. But I can’t this for row 1 and row 3 because the column C is null for row 3. I hope you’ll help me. Thanks.

image
image918×287 7.49 KB

2

HI,

Can you try the following sample?

image
image1074×790 37.7 KB

dict = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("PERSON1").ToString,r("PERSON2").ToString,r("PERSON3").ToString)).Where(Function(g) g.Count>1).ToDictionary(Function(g) String.Join("_",g.Select(Function(r) r("NUMBER").ToString)),Function(g) g.CopyToDataTable)

Sample
Sample20240521-2.zip (14.6 KB)

Regards,

3

Thank you! I can split as I want. But I want to split other values too. I mean row 3 and row 4 should be split one by one. And this is a small example I’ve 600 values like that.

Example:
Excel File 1 → 123456 Mary A. Mark C. Jane K.
654321 Mary A. Mark C. Jane K.

Excel File 2 → 987654 Mary A. Jane K.

Excel File 3 → 456789 Kate B. Mark C. Jane K.

4

HI,

How about the following?

dict = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("PERSON1").ToString,r("PERSON2").ToString,r("PERSON3").ToString)).ToDictionary(Function(g) String.Join("_",g.Select(Function(r) r("NUMBER").ToString)),Function(g) g.CopyToDataTable)

Sample20240521-2 (2).zip (9.7 KB)

Regards,

1 Like
5

Give a try at the following:

Assign Activity:
TableList | DataType: List(Of DataTable) =

(From d in YourDataTableVar.AsEnumerable
Let c1 = d("PERSON 1").toString.Trim
Let c2 = d("PERSON 2").toString.Trim
Let c3 = d("PERSON 3").toString.Trim
Group d by k1=c1, k2=c2, k3=c3 into grp=Group
Select t = grp.CopytoDataTable).ToList

The loop over the TableList Variable and process the split Data

6

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