topic RE: Filter set value in different columns in Product Forum

Filter set value in different columns

Jrwork — Thu, 09 Jun 2022 12:09:00 GMT

Hello masters and mistresses!

I have a problem, well, I have a defined data called "material" and only the value called "center" is in the worksheet, example:

Column C "Material" : "VALUE A", I need to check in column H to Z if it has the center "H001".

The problem is that it is taking a long time and the process sometimes falls because the columns are big.

I had seen something related a while ago here in the community but I couldn't find the topic, someone had made available a code in C# or VBO that helps with this issue.

Can someone help me?

Thanks

------------------------------
José Francisco Bezerra Nunes Júnior
------------------------------

RE: Filter set value in different columns

John__Carter — Thu, 09 Jun 2022 12:43:00 GMT

Hi Jose - you can apply a filter to BP collection with the Utility - Collection Manipulation VBO. The filter expression would be something like
[Material]='Valor A' AND ([CENTRO1]='H001' OR [CENTRO2]='H001' OR [CENTRO3]='H001' OR ...etc)

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

RE: Filter set value in different columns

Jrwork — Thu, 09 Jun 2022 14:48:00 GMT

Hi John! Thanks!

but there's only one problem
the centers are very today the worksheet has 177 columns
And tomorrow it may have 90 or even 200, the number of columns will always change and I don't know how many columns it will have tomorrow, can you guide me?

it would have to be something that filters according to the informed range, something like that, some code..

Obs> Sorry for not posting this question in the thread

------------------------------
José Francisco Bezerra Nunes Júnior
------------------------------

RE: Filter set value in different columns

MichealCharron — Thu, 09 Jun 2022 20:20:00 GMT

@Jose Francisco

Expanding on the reply from @John Carter, you could always use a few stages to build your filter based on the varying columns in your collection. And it all takes a couple of seconds to run.

Use the "Get Collection Fields" action in the "Utility - Collection Manipulation" VBO. This will produce a collection (Fields Collection) of the existing field names in the collection.
Use the "Filter Collection" action in the "Utility - Collection Manipulation" VBO to filter out the fields you want to filter on later into another collection (CENTR Fields). The "Filter" parameter would have the expression:
"[Field Name] LIKE 'CENTR*'"
Use the "Delete Column" action in the "Utility - Collection Manipulation" VBO to delete the "Data Type" field from the "CENTR Fields" collection.
Use the "Rename Field" action in the "Utility - Collection Manipulation" VBO to rename the "Field Name" field to "Item Value" because that is what the next action expects for a collection.
Now is the tricky part, using the "Join Text" action in the "Utility - Strings" VBO, we can build out a filter fragment (Joined Text) using all the desired fields in the "CENTR Fields" collection. The "Join Character" parameter would have the expression:
"]='H001' OR ["
To finish it off we use the "Filter Collection" action in the "Utility - Collection Manipulation" VBO to filter out all the rows with "Valor A" and "H001" in any of the 90-200 fields in your collection. The "Filter" parameter would have the expression:
"[Material] ='Valor A' AND ([" & Left([Joined Text], Len([Joined Text])-5) & ")"

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: Filter set value in different columns

John__Carter — Fri, 10 Jun 2022 08:21:00 GMT

Hi Jose - if you can't predict the columns, then I would try something like Michael's suggestion. Maybe an easier to achieve variant would be to loop through the fields collection and build up a filter statement using a Calc, and then at the end of the loop apply the filter to the data. But Michael's idea looks good too.

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

RE: Filter set value in different columns

Jrwork — Tue, 21 Jun 2022 12:57:00 GMT

@MichealCharron

Thank you very much for your explanation and for being very detailed, follow your thank you for your compliment, thank you very much

------------------------------
José Francisco Bezerra Nunes Júnior
------------------------------