Complex filter

[Wynn]

Hi! I'm trying to filter specific rows in the attached dataset but it seems to be way beyond my knowledge. Could anyone help me to filter out :

1/ any rows that has : "subtotal:" and then name of the entity. Right now, the dataset has both "subtotal: numbers" and "subtotal: text" and i just want to have the rows containing "subtotal " then name of the entity 

2/ on column 2, there is a row containing the text" grand total" . Is there a way to pull the rows above this row? My actual data has 1000 rows called " grandtotal" and I just need only 1 row above it . Is there a tool to pull this specific row? As this specific row contains only numbers , I just don't know what tools/criteria to use to just pull this row only?

See attached workflow and input

Thanks so much!

[VianneyM · Accepted solution]

Hi @Wynn,

Attached you can find a workflow that contains your logic described.

I tool the liberty to clean a bit more your initial data (you don't have to do it, I did it to work with more clean data)

As you can see I used a text to column to separate the values that the column '2' has. In this case subtotal and number/text. With a formula I test if there is a number and then with the filter I separate the subtotals with numbers from the ones with text

For the second question I applied a formula tool to know where is the Grand total row, I marked with a value to identify it. Then with the multirow formula I test if that value exists, if that is the case I provide another identifier to a new column to know that the row-1 is the know I want.

The advantage of doing it like this and not by row number is that regardless of the number of rows, this logic will always work

Let me know if you have any questions

Best,

Vianney 

[Wynn]

Hi Vianney! This worked perfectly. Thank you so much for your help! Appreciate this!