Dictionary vs Filter (Excel Advanced)

So many answers… so little time.  😂

Your question is quite broad, so let’s focus a bit.

Let’s say you have an Excel spreadsheet and you only want the people in Sales. You can loop through all the rows, “filtering” the Sales people by using an IF action inside the loop.

If you need to re-filter, that’s where performance becomes a bit different. You could close and re-open the file, changing the IF for another word to filter upon, but that’s pretty slow.

If you’re talking fewer than thousands of records, you could read the Excel file into a data table. Repeatedly looping through a data table is still quite fast. The restriction here is memory.

There are many, many ways to solve this problem. Can you be a little more specific with your use case?

I need to iterate through 100+ keywords, applying filters individually to aggregate their total values, then repeat the process for the remaining keywords. The values for each keyword are located in a specific column and may span multiple rows per keyword.

I would suggest to store excel data in a table variable and then loop through it as many times as you can. We had excel with thousands of records and it worked well.

If you still feel it's a bit burdensome, you can apply the same logic using VBA and then invoke it in Automation360.

Besides delays and data cleanup, is there a way for the bot to read data tables faster?

If you want this to be working faster, i would recommend you to use excel as a Database. This is way faster and reliable.