How to filter one column with input value and update value of other column without loop and it should return original datatable not filtered datatable

[Mansi_Mhatre]

Hi,
I have one excel sheet or datatable, where i want to perform some filter operation on different columns one by one and update other column values as per requirement without loop as it contains more than 50000-70000 data and after filtering all operations it should return original datatable with updated values not filtered datarows i.e. if filtered operation performed on 25000 rows of 60000 datatows then it should return original datatable with updated values not just 25000 rows datatable.

Sample Dt-

I have one sheet which contains input value and column name i need to filter and output value which needs to update in output column.
Template sheet format:(this will be used in for each row activity)

So i want to check Template sheet and filter inputcoulmn with input value, if any match found then update given output value in column ‘Remarks’ and value “Yes” in Reference column.

Note: Input datatable contains more than 50000-70000 data so i want without looping the input datatable. We only looping template sheet for input values.

Output should be:

[ashwin.ashok ((TheCodingTheory))]

Hi @Mansi_Mhatre ,

Is this what you were looking for?

If so, then you have to declare a Dictionary(Of String, Tuple(Of String, String))->

Convert the second sheet into a Dictionary using this snippet of code->

dt_reference.AsEnumerable().ToDictionary(Function(k) k("Input").ToString, Function(v) Tuple.Create(v("InputColumn").ToString.Trim, v("Output").ToString.Trim))

Then perform the main operation using this snippet of code->

(From row In dt_masterTable.AsEnumerable()
Let Remark = If(dict.Keys.Any(Function(a) row.ItemArray.Select(Function(s) s.ToString.Trim).Contains(a.ToString.Trim)),
	dict(dict.Keys.First(Function(f) row.ItemArray.Select(Function(s) s.ToString.Trim).Contains(f.ToString.Trim)).ToString.Trim).Item2,"")
Let Reference = If(String.IsNullOrEmpty(Remark.ToString),"","Yes")
Let ra = row.ItemArray.Take(4).Concat({Remark,Reference}).ToArray()
Select dt_result.Rows.Add(ra)).CopyToDataTable()

UpdateRemarksSheet.xaml (7.8 KB)

Kind Regards,
Ashwin A.K

[Mansi_Mhatre]

One more problem is there, how Emp Id. Sometimes contains prefix value as E i.e. E1,E2 etc.
How to Remove that prefix and add to same column or new column without loop.

For example:

So i want to remove if starting with E prefix.
We can add new “Emp_Id1” column also for output result.

[Mansi_Mhatre]

Hi getting error while adding to dictionary tuple as An item with the same key has already been added.

Getting error if same input i want to search in different column has been placed.

How to resolved this issue.
If bcd i want to search in two different columns how should we so that any solution kindly help.