Remove higher values

ivoiculescu2020

Dear team,

I need your help with these 2 situations:

1. I have this report that lists some documents and the versions we have in our systems. For example, the documents can have these versions: 1.A, 2.A, 3.A etc... However, I need to remove from the report any versions that are 2.A and higher. I used a Filter and it did the job. However, I need to know if it's an easier/more direct way especially because the versions will go much higher than 3.A.

2. I need to remove from the report any rows that contain certain words: for example, any row that contains "GB", "JP" etc. I used a Filter again to filter out these rows but I was wondering if there is an easier way because right now my workflow is quite messy and I am trying to clear it a little bit. Is there another function I could use to remove the rows that contain these "keywords"?

Many thanks in advance!

Kind regards,

Ioana

Preparation

Accepted answers

Emil_Kos

Hi @ivoiculescu2020,

Depening if the list of the countries that you want to exclude I would pick two different approaches. If it is big or it is a short one I would pick one way or another. @atcodedog05 approach will work really well if you have a small list of countries that you want to exclude.

If you have a big list of items that you want to filter out that or/additionally the list is changing in time I would add a find replace tool in your solution.

Please see my solution for reference. In the workflow, you can keep the list of the countries that you want to exclude and having a simple table like this can make your life easier in a long run.

The downside of it is that the find and replace tool propably will be slower if you have a big amount of data but it is definitely easier to maintain.

Best Practices.yxmd

Elias_Nordlinder

Hello @ivoiculescu2020 ,

(PS, did not see that @atcodedog05 already had a quicker solution with the filter tool directly,

so go for the solution below mine, but both works 😉 )

I think something like this would work for that, leveraging the regex tool.

Use the Regex Tool with the formula "(\d+)", it will parse out the digits from the string.

Use Filter tool to only filter on where this number is 1 and where the original string contains JP or CHN.

The result should be all the strings which only have the value 1 and is not JP or GB

Remove Higher Values.yxmd

atcodedog05

Hi @ivoiculescu2020

My Bad. Fixed it

(REGEX_Replace([Document name / Version], ".+(\d+)\.A", "$1")<"2")

AND

(!Contains([Document name / Version], "GB") and
!Contains([Document name / Version], "JP"))

Workflow:

Hope this helps : )

filter.yxmd

All comments

atcodedog05

Hi @ivoiculescu2020

Can you provide sample data and expected output. We would be able to help you much better 🙂

ivoiculescu2020

@atcodedog05 Hello! My apologies.

Here it is a sample. I would normally use Filter multiple times to remove any rows that have GB, JP and any versions that are 2.A and higher. But the workflow looks long and messy. Is there a much direct way to remove the rows I don't need. The resulting report should only have these rows: 5, 6, 8 and 9.

Thank you!!!

Sample.xlsx

Emil_Kos

Hi @ivoiculescu2020,

If you have a big list of items that you want to filter out that or/additionally the list is changing in time I would add a find replace tool in your solution.

Please see my solution for reference. In the workflow, you can keep the list of the countries that you want to exclude and having a simple table like this can make your life easier in a long run.

The downside of it is that the find and replace tool propably will be slower if you have a big amount of data but it is definitely easier to maintain.

Best Practices.yxmd

ivoiculescu2020

@atcodedog05 @Elias_Nordlinder @Emil_Kos

thank you all for your kind assistance! all your solutions work perfectly. The report is small for now (100-200 rows) but it has the potential to grow massively over time. So I am glad to have multiple solutions for both situations.

Have a wonderful day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703