Filter,VLookup and merge datatable

Hi,

I need to filter my excel based on certain column value, i need to perform vlookup on filtered datatable and then i need to merge original unfiltered datatable with filtered data table(after vlookup).

For this, i followed below steps:

1.Checked if certain value exists in a column,
2. If row value exists,i took a workbook read range activity and Filtered datatable using “Filter DataTable” activity and stored the output in new datatable
3. Performed Vlookup using lookup datatable acivity

Now while writing vlookup output, i need to give a workbookpath where i need to write. here how can i write my vlookup output cells to my filtered datatable

can anyone suggest?

Hi @Sirisha_Siri ,

If you are trying to perform conditional checks, that can be achieved using this:

Dt.AsEnumerable().Where(Function(w) w(IndexOrColumnName).ToString.Trim.Equals(Condition)).CopyToDataTable()

But if you have already performed those steps and only interested in merging the result data with the existing one, you could use an Append Range Activity instead and pass in the cell address as :

"B"+(dt.Rows.Count+2).ToString

If this is not what you were looking for then please provide an example so that we can help you resolve the query.

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

I will give an example of my usecase on what i am trying to do.

image

I need to extract the document number of Z_Receive goods, with corresponding serial number

Hi @Sirisha_Siri ,

If its just to retrieve the respective columns, could you then check if the code below works out for you?

dt_sampleData.AsEnumerable.Select(Function(s) dt_result.LoadDataRow({s("Document"),s("Material")},False)).CopyToDataTable()

ExtractSelectColumns.xaml (9.1 KB)

Kind Regards,
Ashwin A.K

No, its not giving required output. serial number will be same for z_receive, z_shipment and z_transfer. but i need to get value of material document of only z_receive. similarly there will be several serial numbers having z_receive,z_shipmen,z_transfer. for all the serial numbers, only material document associated with z_receive has to be extracted.

Hi @Sirisha_Siri ,

Could you try this instead?

dt_sampleData.
	AsEnumerable.
	Where(Function(w) w(0).ToString.Trim.ToLower.Contains("z_recieve")).
	GroupBy(Function(g) g("Serial Number").ToString).
	Select(Function(s) dt_result.LoadDataRow({s.First.Item("Document"),s.First.Item("Material")},False)).CopyToDataTable()

ExtractSelectColumns_v1.xaml (10.4 KB)

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok

it is giving blank value

image

Hi @Sirisha_Siri ,

There seems to be a spelling mistake, could you try z_receice instead of z_recieve?

dt_sampleData.
	AsEnumerable.
	Where(Function(w) w(0).ToString.Trim.ToLower.Contains("z_receive")).
	GroupBy(Function(g) g("Serial Number").ToString).
	Select(Function(s) dt_result.LoadDataRow({s.First.Item("Document"),s.First.Item("Material")},False)).CopyToDataTable()

Kind Regards,
Ashwin A.K

Not working