Remove rows based on condition

[FECL3]

Hi

I'm trying to remove rows based on a condition, whereby once it is found i then want to remove that row and then all 4 rows beneath it. I have considered using the multi-field formula tool and then create a flag which can then be filtered out. My issue is I don't know how to configure the multi-field formula tool. 

Please see example data below. I want to remove all 3 rows under Total for Year as well as the Total for Year row.

Your help would be much appreciated

Name	Value
Week 1	10
Week 2	20
Week 3	30
Week 4	40
Week ....
Total for Year
Sales	500
Cost	400
Profit	100

[Jonathan-Sherman]

Hi @FECL3,

Could you not just use the StartsWith() function?

StartsWith([Name], 'Week')

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[FECL3]

No, the above is a sample data so that won't work as there are duplicates fields. A better example of the data format would be this:

	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5
Total for Month 1
Sales	40
Cost	20
Profit	20

In effect the table at the bottom is a summary of the detailed table above, but I need to remove it and it's difficult because the naming convention is the same as the above. The only unique identifier is the header, which comes out similar to the "Total for Month"

Thanks

[Jonathan-Sherman]

Hi @FECL3,

In that case it could be achieved with a multi-row formula and a filter tool:

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[FECL3]

Thank you. Although this solution won't work if there are more tables that I need below. I need the solution to be more precise. 

I am dynamically inputting in multiple files into one workstream so there is more data below the table.

Thanks again

[mceleavey]

Hi @FECL3 ,

@Jonathan-Sherman 's solution is correct with the data you and scenario you've provided. If you want anything else, you need to provide a representative dataset to cover these scenarios.

Can you please provide a dataset that is representative and covers the issues you're having?

M.

[FECL3]

Hi @mceleavey 

Please see dataset below.  Please bare in mind that there can be multiple iterations of this as I am dynamically

Thanks

	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5
Total for Month 1 - Product 2
Sales	40
Cost	20
Profit	20
	Week 1	Week 2	Week 3	Week 4
Sales	10	10	10	10
Cost	5	5	5	5
Profit	5	5	5	5
Total for Month 1 - Product 1
Sales	40
Cost	20
Profit	20

[mceleavey]

@FECL3 ,

...and, what do you want to see? That data is a different layout to the previous request.

M

[mceleavey · Accepted solution]

@FECL3 ,

I've attached a workflow that will now dynamically pull the data you need and create the following format:

This will handle any number of tables. If you're dynamically loading them in though, it sounds like you could do this before smooshing the data together.

Anyway,

I hope this helps,

M.