I have a problem statement to remove a row from excel where the subject and date cell equals to the other subject and row cell in another row. Basically, my idea is to have only one row where the subject is unique based on the date.
@mukesh_behera
working with this data which is similar to your case:
deletion on excel can be done with delete row activity using the row index.
We can retrieve it with this LINQ:
(From t In dtData.AsEnumerable.Select(Function (x,i) Tuple.Create(i,x))
Group t By k1=t.Item2("Subject").toString.Trim, k2=t.Item2("Date").toString.Trim.Substring(0,10) Into grp=Group
Where grp.Count > 1
Let il =grp.Skip(1).Select(Function (tg) tg.Item1).toList
Select il).SelectMany(Function (sm) sm).OrderByDescending(Function (o) o).toList
Result (for this sample data):
Afterwards we can use this list within a for each and delete the rows. For excel we have to take about the offset as e.g. it has to be corrected for 1row= header name
LINQ: (From d In dt_Input.AsEnumerable Group d By k1=d(1).ToString.Trim, k2=d(2).ToString.Trim Into grp=Group Let a = grp.Select(Function ( r ) r(0).toString.Trim).ToList.First Let b = CDate(k2.ToString).ToString("dd-MM-yyyy") Let row = New Object() {a, k1, b} Select dt_Output.Rows.Add(row)).CopyToDataTable