Hello,
here is the excel data i have
i need to remove line number 2 as Ticket Key column value is duplicated and time(consider date as well) is previous than next row time.
so need to remove only 2 nd row and keep rest datatable same so expected output is -
how can i achive this?
Try this LINQ:
(From row In dt_Data.AsEnumerable()
Group row By k=row("Ticket Key")
Into grp=Group
Select grp(0)
).CopyToDataTable
or below LINQ:
dt = dt.DefaultView.ToTable(True, "Ticket Key","Work Order","Time","Status")
Regards
Parvathy
how can i use this ?
in assign activity? can i write in one line?
and is it able to delete data only with previous time ?
Try this:
result =
(From row In dt.AsEnumerable()
Group row By key = row("Ticket Key").ToString
Into grp = Group
Select grp.OrderByDescending(Function(r) DateTime.ParseExact(r("Time").ToString,"dd/MM/yyyy HH:mm", System.Globalization.CultureInfo.InvariantCulture)).First()
).CopyToDataTable()
Regards
PS Parvathy
=> Use Read Range Workbook to read the input and store it into data table variable say dt.
=> Use the below syntax in Assign activity
dt_Result = (From row In dt.AsEnumerable()
Group row By key = row("Ticket Key").ToString
Into grp = Group
Select grp.OrderByDescending(Function(r) DateTime.ParseExact(r("Time").ToString,"dd/MM/yyyy HH:mm", System.Globalization.CultureInfo.InvariantCulture)).First()
).CopyToDataTable()
dt_Result is of datatype System.Data.Datatable
=> Use dt_Result datatable for your further process or write it in excel using Write Range Workbook.
Sequence6.xaml (7.2 KB)
Regards
PS Parvathy
tried as per your suggestion.
getting below error-
Follow the below steps
Use read range workbook and store the output as dtInput
Take assign activity create a variable dtOutput - variable type should be DataTable and Pass that in To section of Assign activity
In Value section pass below value
dt_Output = dt_Input.AsEnumerable.GroupBy(Function(r) r(“Ticket Key”).ToString).Select(Function(g) g.OrderByDescending(Function(r) DateTime.Parse(r(“Time”).ToString)).First).CopyToDataTable
Now use write range work book pass this dtOutput variable over there.
Hope this helps,
YK
Try this
dt= dt.AsEnumerable().
GroupBy(Function(r) r(“Ticket Key”).ToString).
Select(Function(g) g.OrderByDescending(Function(r) DateTime.Parse(r(“Time”).ToString)).First()).
CopyToDataTable()
Pending Tickets.xlsx (9.3 KB)
Output is in excel sheet2
Hope it helps!!
Try this syntax:
dt_Result =
(From row In dt.AsEnumerable()
Group row By key = row("Ticket Key").ToString
Into grp = Group
Select grp.OrderByDescending(Function(r) DateTime.ParseExact(r("Time").ToString,"dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)).First()
).CopyToDataTable()
Regards
Parvathy
have you tried with my excel ?
I tried with your excel.
Below is the syntax:
(From row In dt.AsEnumerable()
Group row By key = row("Ticket Key").ToString
Into grp = Group
Select grp.OrderByDescending(Function(r) DateTime.ParseExact(
r("Time").ToString,
"MM/dd/yyyy HH:mm:ss",
System.Globalization.CultureInfo.InvariantCulture
)).First()
).CopyToDataTable()
Regards
Parvathy
@Mathkar_kunal Please try the below steps:
Step 1: Read the Excel file
Use Read Range Workbook or Excel Read Range activity.
Output DataTable:
dt_Input
Your dt_Input will contain data like:
Ticket Key | Work Order | Time | Status
Step 2: Create one output DataTable variable
Create a new variable:
dt_Output, Type: System.Data.DataTable
Step 3: Add Assign activity
In the Assign activity, use this:
dt_Output = dt_Input.AsEnumerable().
GroupBy(Function(row) row(“Ticket Key”).ToString.Trim).
Select(Function(group) group.OrderByDescending(Function(row) DateTime.ParseExact(row(“Time”).ToString.Trim, “dd/MM/yyyy HH:mm”, System.Globalization.CultureInfo.InvariantCulture)).First()).
CopyToDataTable()
Step 4: What this logic does
It will first group the rows based on Ticket Key.
For example:
EJCS-27089
is available two times.
Then it will compare the Time column:
25/05/2026 12:26
25/05/2026 13:25
Since 13:25 is the latest time, it will keep that row and remove the older row.
So this row will be removed:
EJCS-27089 | User Addition on Confluence Space | 25/05/2026 12:26 | Pending
And this row will be kept:
EJCS-27089 | User Addition on Confluence Space | 25/05/2026 13:25 | Pending
Step 5: Write the final DataTable back to Excel
Use Write Range Workbook activity.
Input DataTable: dt_Output
I have created the workflow for your reference with your excel, please find the below
ForumTest.zip (220.1 KB)
Hope it helps,
YK
Please try the below steps:
Step 2: Create one output DataTable variable
Step 3: Add Assign activity
dtOutput = dtInput.AsEnumerable().
GroupBy(Function(row) row(“Ticket Key”).ToString.Trim).
Select(Function(group) group.OrderByDescending(Function(row) DateTime.ParseExact(row(“Time”).ToString.Trim, “dd/MM/yyyy HH:mm”, System.Globalization.CultureInfo.InvariantCulture)).First()).
CopyToDataTable()
Step 4: What this logic does
It will first group the rows based on Ticket Key and will compare the Time column, keep row with latest time and remove the older row.
So, this row will be removed:
EJCS-27089 | User Addition on Confluence Space | 25/05/2026 12:26 | Pending
Step 5: Write the final DataTable back to Excel
Use Write Range Workbook activity and pass input - dtOutput.
Take assign activity instead of copy pasting to write expression manually and also add the namespaces related linq. Open the namespaces/import there just linq and click on the linq related namespaces then you can try.
Let me know if it works.
Would you mind sharing your excel file?
Pending Tickets.xlsx (8.8 KB)
here is the file
