For Each Row On Excel Data That Was Filtered

Hello everyone,

I have 2 excel files, One is a list of values (Excel A) and the other is the data set I am trying to find these values (Excel B). What I would like to do is take the values from Excel A and use them to filter Excel B for only that value, then copy the rows back to Excel A.

To do this, I have a for each row on Excel A, then filter Excel B, then For each row in Excel B (nested in the first for each row) copy the values to Excel A. This works, but it takes a very long time.

I can see Excel B filtering and there are only 1-10 active cells, but I believe that it is still going through every line in Excel B. How can I make it so it will only go for each row after the data is filtered?

Thanks

@Marc_Sciarrino
Can you read Excel B into Data Table, then use Filter Data Table by Excel A value, then write it into Excel A?

Regards

I am fairly new to this, so I can look into that. My Excel B table has about 140k rows, will that cause issues?

@Marc_Sciarrino
No issues, just use a Read Range activity on Excel B into data table, then use Filter Data Table activity which will create a new data table filtered, then Write Range the filtered data table to Excel A.

Regards

Thank you for your help. I will try this.

I have been trying this today and I cannot figure out how to filter the data table. The only thing I can seem to do with the data table I have stored is write it to excel as. I dont have the option to “use a saved variable” in the filter activity.

@Marc_Sciarrino

Search for filter datatable activity…

And in the filter input table provide dt2 and output as filtereddt…and in filter options …provide the column name in excelB that you want to filter and then on the right give the value from excelA like currentrow("ColumnName").ToString

Thwn you can paste whole of the filtered table to excel A as needed

Hope this helps

Cheers

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