Create new row based on column value

[arina]

I previously asked this question here:

Convert column to row based on one column

I have this table: [image] And I want to convert it to this: [image] The key is column W*: If its value is E or S, it will be grouped into one row for the same Cust. Meanwhile, if the value is N, each N will be a new row. Attached is the input file. Status.xlsx (12.0 KB)

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)

[Yoichi]

Hi,

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

Sample20220829-1.zip (13.0 KB)

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

Regards,

[arina]

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?

[Yoichi]

Hi,

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

Sample20220829-1v2.zip (13.5 KB)

Regards,

[arina]

thank you it works wonderful !!