Create new row based on column value

I previously asked this question here:

But I unable to modify it to my expected output. The difference between the last time is that now I want to group it all in one row by Customer and by Warehouse value then recalculate the total amount per row.

Input & Output.xlsx (12.3 KB)


How about the following sample?

First, to handle easier, create datatable listed each single item, using the following expression.

dtSingle = dt.AsEnumerable.SelectMany(Function(r) Enumerable.Range(0,14).Select(Function(i) dt.Clone.LoadDataRow(r.ItemArray.Take(4).Concat(r.ItemArray.Skip(4+i*5).Take(5)).ToArray,False))).Where(Function(r) not String.IsNullOrEmpty(r("Item 1").ToString)).CopyToDataTable

Then Grouping by Customer and Warehouse and put item in same group into single row.

dtResult = dtSingle.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Customer").ToString,r("Warehouse 1").ToString)).Select(Function(g) dtSingle.Clone.LoadDataRow(g.First.ItemArray.Take(4).Concat(g.SelectMany(Function(r) r.ItemArray.Skip(4).Take(5))).ToArray,False)).CopyToDataTable (13.0 KB)

Result will be output in sheet “input” in result.xlsx


let’s say if i have 20 items with warehouse B in a Customer, and i want to limit it to only 8 item per row so I have to split them to three rows consists of 8, 8, 4, how do i manipulate dtResult?


Can you try the following expression?

dtResult = dtSingle.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Customer").ToString,r("Warehouse 1").ToString)).SelectMany(Function(g) Enumerable.Range(0,((g.Count-1)  \ 8) +1).Select(Function(i) dtSingle.Clone.LoadDataRow(g.First().ItemArray.Take(4).Concat(g.Skip(i*8).Take(8).SelectMany(Function(r) r.ItemArray.Skip(4).Take(5))).ToArray,False))).CopyToDataTable (13.5 KB)


thank you it works wonderful !!

1 Like

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