C#: How to get the next row item and compare it with current row item? Using For each Excel Row

[steph_C]

I would like to compare the currentRow item with the next line item in the same column.
e.g. A3 compare with A4, if same then join related info, or else compare A4 with A5.

How i can get the table with the name and expected result? Thank you.

[Yoichi]

Hi,

In this case, we can use LINQ GroupBy for it as the following.

dt = dt.AsEnumerable().GroupBy(r=>r["Name"].ToString()).Select(g=>dt.Clone().LoadDataRow(new object[]{g.Key,String.Join(" and ",g.Select(r=>r["Fruit"].ToString()))},false)).CopyToDataTable()

Sample20230202-2CS.zip (9.1 KB)

Regards,

[steph_C]

It works pretty well, and i would like to ask how to add one more column “Toys” with the same result as “Fruit”? and more columns (“Age” and “Grade”) in the final result.

Expected Result

What should be the C# LINQ programming?
Many thanks!!!

[Yoichi]

HI,

How about the following?

dt = dt.AsEnumerable().GroupBy(r=>r["Name"].ToString()).Select(g=>dt.Clone().LoadDataRow(new object[]{g.Key,g.First()["Age"].ToString(),String.Join(" and ",g.Select(r=>r["Fruit"].ToString())),String.Join(" and ",g.Select(r=>r["Toys"].ToString())),g.First()["Grade"].ToString()},false)).CopyToDataTable()

Sample20230202-2CSv2.zip (9.9 KB)

Regards,