Split Datatable with LINQ

Hello,
I have a Datatable myDt with 2 columns Name and Quantity, with 4 rows for example. I want to split this Datatable in 3 parts (to be copied in 3 Sheets in a workbook). The idea is to have disctinct name in each Sheet, with the quantity divided by the number of sheets (3 in this example). So I think that I could use Sum, Distinct, Group By, etc. with Linq, but I don’t know how to proceed.
For example with this input:
John - 8
John - 4
Paul - 6
Elvis - 3

I want this output:
Sheet 1
John - 4
Paul - 2
Elvis - 1

Sheet 2
John - 4
Paul - 2
Elvis - 1

Sheet 3
John - 4
Paul - 2
Elvis - 1

I could have a lot of lines, so to recap, for each case, I need to have distinct name on each sheet, I need to add the quantity when I get identical names and then I need to divide the quantity by the number of sheets requested.
Thank you for your help.

your output samples are not clear. But in general it looks like a groupBy case

Thanks PPR, I know this topic but I can’t find the correct request.
The output is to divide the quantity by 3 in this example.
In the main Input I have 6 for Paul so I need to divide by 3 that’s why I have 2 in the output. For Elvis I have 3 so I need to divide by 3 so I have 1 for Elvis, but for John, because I have 2 rows, I need to add first the quantity 8+4= 12 then to divide by 3 so 12/3 = 4
The output will be on each Sheet:
John - 4
Paul - 2
Elvis - 1

when we can assume that doubles are ok e.g. we could do:

Assign Activity:

Assign Activity
iCount | int32 =
dtDataVar.AsEnumerable.Select(Function (d) d("NameCol").toString.Trim).Distinct().Count

Assign Activity
dtTemplate = dtDataVar.Clone

Assign Activity
dtTemplate =

(From d in dtDataVar.AsEnumerable
Group d by k=d("NameCol").toString.Trim into grp=Group
Let cs = grp.Sum(Function (s) CInt(s("NoColName").toString.Trim))
Let ra = new Object(){k,cs/iCount }
Select r = dtTemplate.Rows.Add(ra)).CopyToDataTable 

Now you can write out iCount times dtTemplate to an Excel

1 Like

Thank you so much PPR, I get the result that I want

One last thing, how could I proceed to get the first part:
dt_Input is this datatable
Name| Quantity
John | 8
John |4
Paul | 6
Elvis |3

How could I get a dt_Result datatable with:
Name| Quantity
John | 12
Paul | 6
Elvis |3

(From d in dtDataVar.AsEnumerable
Group d by k=d("NameCol").toString.Trim into grp=Group
Let cs = grp.Sum(Function (s) CInt(s("NoColName").toString.Trim))
Let ra = new Object(){k,cs}
Select r = dtTemplate.Rows.Add(ra)).CopyToDataTable 
1 Like

Thank you so much PPR

I would like to be more familiar with Linq and I would like to understand how I could pass all the columns if I had a datatable in input dt_MyDt like this:
Col1 | Col2 | Col3 | Col4 |
John | 8 | UK | London
John | 4 | UK | London
Paul | 6 | FR | Paris
Elvis | 3 | SP | Madrid
Elvis | 6 | SP | Madrid

Output
Col1 | Col2 | Col3 | Col4 |
John | 12 | UK | London
Paul | 6 | FR | Paris
Elvis | 9 | SP | Madrid

Thanks

Please open for any next question a new topic as we prefer to have scoped 1 Topic = 1 Case

(From d in dtDataVar.AsEnumerable
Group d by k=d("NameCol").toString.Trim into grp=Group
Let cs = grp.Sum(Function (s) CInt(s("NoColName").toString.Trim))
Let ra = new Object(){k,cs, grp.First()("Col3"), grp.First()("Col4")}
Select r = dtTemplate.Rows.Add(ra)).CopyToDataTable 

[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

1 Like

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