Filter datatable based on criterias

Hi,
I have a datatable, and I have to filter it based on some criteria.
1st Criteria If Cost = 10 and Branch = 1502 filter column Item with unique values(It contains repeated values just get them once)
2nd Criteria If Cost = 10 and Branch = 1503 filter column Item with unique values(It contains repeated values just get them once)
3rd Criteria If Cost = 07 and Branch = 1502 filter column Item with unique values(It contains repeated values just get them once)
4th Criteria If Cost = 07 and Branch = 1503 filter column Item with unique values(It contains repeated values just get them once)
I have attached the Input Sheet for the same.
InputSheet.xlsx (10.2 KB)
According to the input we will get 4 datatables as output.
I have attached the output sheet for the same.
OutputSheet.xlsx (11.4 KB)
Please let me know how to solve the issue?
(Mainly how to get the non repeated values for the 1st column)
Thanks in advance!!

Hi @Kunal_Jain,

You can use the “remove duplicate rows” activity before you write the data into excel so that, it does not contains the repeated values.

Regards,
Sandhya.

Hi @Govindharam_Sandhya
I know we can delete the data using Remove duplicate rows but is there any other way by which all the criteria’s can be completed at once.
Cheers

@Kunal_Jain,

You can try below function for filter the values at a time then you can use the remove duplicate rows activity:

DT.AsEnumerable().Where(Function(row)(row(“Cost”).ToString.Equals(“10”)) AndAlso
(row(“Branch”).ToString.trim.Equals(“1502”) Or (row(“Cost”).ToString.Equals(“10”)) AndAlso
(row(“Branch”).ToString.trim.Equals(“1503”) or (row(“Cost”).ToString.Equals(“07”)) AndAlso
(row(“Branch”).ToString.trim.Equals(“1502”) or (row(“Cost”).ToString.Equals(“07”)) AndAlso
(row(“Branch”).ToString.trim.Equals(“1503”))).CopyToDatatable()

hey @Kunal_Jain

Let’s assume the following column names: Column1 as “Cost”, Column2 as “Branch”, and Column3 as “Item”.

  1. Cost = 10 and Branch = 1502
filteredDt1 = dt.AsEnumerable().
    Where(Function(row) row("Column1").ToString() = "10" AndAlso row("Column2").ToString() = "1502").
    GroupBy(Function(row) row("Column3").ToString()).
    Select(Function(g) g.First()).
    CopyToDataTable()
  1. Cost = 10 and Branch = 1503
filteredDt2 = dt.AsEnumerable().
    Where(Function(row) row("Column1").ToString() = "10" AndAlso row("Column2").ToString() = "1503").
    GroupBy(Function(row) row("Column3").ToString()).
    Select(Function(g) g.First()).
    CopyToDataTable()
  1. Cost = 07 and Branch = 1502
filteredDt3 = dt.AsEnumerable().
    Where(Function(row) row("Column1").ToString() = "07" AndAlso row("Column2").ToString() = "1502").
    GroupBy(Function(row) row("Column3").ToString()).
    Select(Function(g) g.First()).
    CopyToDataTable()
  1. Cost = 07 and Branch = 1503
filteredDt4 = dt.AsEnumerable().
    Where(Function(row) row("Column1").ToString() = "07" AndAlso row("Column2").ToString() = "1503").
    GroupBy(Function(row) row("Column3").ToString()).
    Select(Function(g) g.First()).
    CopyToDataTable()

Hi @Kunal_Jain ,

Here you go.
ForKunal.xaml (11.1 KB)

Use LINQ for filters like

dt1 = dtInput.Select("[Cost]=07 AND [Branch]=1503").CopyToDataTable.AsEnumerable.Distinct(DataRowComparer.Default).CopyToDataTable

Oce we have all DT write it with Write Range

Thanks,
Ashok :slight_smile:

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