Filter set value in different columns

[José_FranciscoB]

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
------------------------------

[John__Carter]

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
------------------------------

[José_FranciscoB]

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
------------------------------

[MichealCharron · Accepted solution]

@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
------------------------------

[John__Carter]

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
------------------------------

[José_FranciscoB]

@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
------------------------------