Filtering 2 Collections and Returning Results

[WillemNel]

Hi All

Just wondering if someone will be able to advise me on the following.

I have two excel spreadsheets one is a bank upload excel file and the other excel file is a list of funds.
I imported them both into Blue Prism and created two collections. Collection IM Numbers and Collection Funder

Collection funder has got a list of funds that are electronic and manual.
I need to remove the electronic funds from the banking file so that the info that is left can be loaded into the Queue.

I think I will have to use a loop but when I use a wildcard with a data item to search the specific column I keep on receiving errors.

The text that I am using to filter is as follow  - "[Allocation] Like 'BAR*'" and then I receive results but if I replace the BAR with a data item from the other collection I receive an error. I need to use a wild card as the start of the description will always be the same and the end will differ.
Please see screens below:

Change the data Item

Error

I am not sure if I am doing it in the correct way as I am still new to development.

Thanks.

------------------------------
Willem Nel
------------------------------

[Anonymous]

When working with Excels I don't find safe to put all the data in collections. Better make a temp file put each file into each spreadsheet1 and 2 and than to vlookups with what you need. In Excel do all the logic and in the end, when you have the required results you can put them in collections. I found this way of doing more satisfying than the collection stuff...

------------------------------
Cohen
RPA Developer

Romania
------------------------------

[John__Carter]

Hi Willem - it's possible to query Excel sheets like database tables using an OLEDB query. So you may be able to use an SQL statement to get the results you want in one step. Admittedly it's more technically challenging but should be far more efficient than filtering and looping.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------