Pulling data from one sheet to filter another sheet

Help
,
1
  1. I have a table in an excel sheet where Column 2 contains the following codes :

001
002
003
004
005

  1. I have a table in another excel workbook (the master workbook) in which Column 2 contains codes from 001 to 500 (codes are repeated).

I need to pull all the codes from Step 1 and use them to filter Column 2 of the master workbook. Once I do this, I need to write that filtered table into a new sheet in the master workbook itself.

Note : Codes in Step 1 are dynamic and may vary between 001 and 500. There could be n number of codes (not just 5 as demonstrated above).

How can we achieve this ?

Kindly help.

2

Hi,

Here is one approach:

  1. Store column in first table to an array
  2. Filter master table using Array.Index() to see if the value is contained in that array.

The one-liner to accomplish this would look something like this:
dt1 is first table, dtMaster is master table

dtFiltered = dtMaster.AsEnumerable.Where(Function(row) Array.IndexOf( dt1.AsEnumerable.Select(Function(code) code(1).ToString.Trim).ToArray, row(1).ToString.Trim ) > -1 ).CopyToDataTable

Then, just use Write Range with dtFiltered and new sheet name

I have not tested the vb.net code but it looks right, and it might need some adjustments.

Regards.

3

Also, instead of this line you can replace with .Contains() like:

dt1.AsEnumerable.Select(Function(code) code(1).ToString.Trim).ToArray.Contains( row(1).ToString.Trim )

Final one-liner with this replacement would be like this:

dtFiltered = dtMaster.AsEnumerable.Where(Function(row) dt1.AsEnumerable.Select(Function(code) code(1).ToString.Trim).ToArray.Contains( row(1).ToString.Trim ) ).CopyToDataTable

EDIT: correction made

1 Like
5

How do I include this in the code ? I don’t see .AsEnumerable.Where when I try to assign “dtFiltered” as a datatable.

Can you please provide me a detailed approach to this ?

Thanks a lot.

6

AsEnumerable will not be listed, so you just need to type it out. (it’s not case-sensitive also)
It basically converts your table into an ienumerable List of DataRows, so it unlocks all the Array and Lambda functions.

you can use this anywhere you want to use a datatable, like you can use it in an Assign activity to store the result to another variable, or you can bypass the Assignment and use it directly in a For each, to loop through each row.

I will also mention that sometimes your .Where function will not find any rows, which then you will receive a “datatable has no rows” error using .CopyToDataTable. In this case, you might consider using .ToArray instead of .CopyToDataTable and you can check its row count with .ToArray.Count (which you can use as a condition before storing to the datatable or processing that data. EDIT: You can also use an Assign activity to store the .ToArray or array variable back to a .CopyToDataTable after you have checked its .Count… if you prefer.

I don’t think you need further details on where to use it now that I mentioned in an Assign or For each, but let me know if you still need more help getting it to work in your workflow and maybe post a screenshot of your issues with it.

Regards.

2 Likes
7

Genius! Sheer genius! Can’t thank you enough Clayton.

This is outstanding! :slight_smile:

8

Hey @ClaytonM ! Need one more help from you. I need to delete this filtered data from the master sheet. How can we achieve this ?

Thanks!

9

Hey @susbasu

Best way would probably be to do the opposite condition, so filter in reverse then overwrite the sheet with new table.

For example,
Instead of the condition being:
code(1).ToString.Trim).ToArray.Contains( row(1).ToString.Trim )

do the opposite, like:
not code(1).ToString.Trim).ToArray.Contains( row(1).ToString.Trim )

full example would be like this:

dtFiltered = dtMaster.AsEnumerable.Where(Function(row) dt1.AsEnumerable.Select(Function(code) not code(1).ToString.Trim).ToArray.Contains( row(1).ToString.Trim ) ).CopyToDataTable

Then, clear the master sheet by using Select All + “Clear All” in the menu ribbon (ie “[k(alt)]hea” performs the Clear All using Alt-key combo), or you can write the table to a new sheet and delete the original

Hope that helps.

Regards.