Hi! When filtering multiple times for each keyword in a spreadsheet, is it better to use the Excel Advanced package or a dictionary? I first used the dictionary package, but it seems impractical with over 100 keywords. Any suggestions for a better approach?
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.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.