Help filtering out all rows below identifier

[agodwin1250]

Hello,

I am wondering if there is a way to have a filter/formula or other tool which you could write an expression for an identifier and have it also filter out and exclude all rows below?

Essentially, my excel data source has multiple tabs, with each tab representing a different equity option grant. Within each tab is 2 tables which are exactly the same format but one is in USD$ and one is in CAD$, and I only need to extract the USD$

There is a header row which simply says "U.S. DOLLARS" above the USD$, and a row which says "CANADIAN DOLLARS" above the CAD$ (2nd table below USD$) - and so I am wondering if I could have a filter or other tool which could identify and exclude the entire 2nd table in CAD$?

Thanks in advance!

[atcodedog05]

Hi @agodwin1250 

How about trying Filter Tool condition field not contain CAD$ and not Canadian Dollars

considering the data is one below other in the same column.

If they are different columns select tool can deselect the CAD$ column

[atcodedog05]

Hi @agodwin1250 

Can you provide sample snapshot of the data so we can help you better.

[hanykowska]

Hi @agodwin1250 

what I would suggest is a multirow formula tool combined with a filter tool:

If 'US Dollars' and 'Canadian Dollars' rows are on their own and a separate column, so you have either empty/null values or the distinction between the dollars, you can edit that field with a multirow formula tool with the following expression:

IF [field]=Null()

then [Row-1:field]

else [field]

endif

then you can add a filter tool, where [field] = 'US Dollars' or whatever is the appropriate value.

Hope that helps and that I understood the question right!

[hanykowska · Accepted solution]

Alternatively, if the 'US Dollars'/'Canadian Dollars' rows are amongst other tables, you can use multirow formula to create a new field

'description field' would be the column with 'US Dollars'/'Canadian Dollars' 

'new field' is the new field

IF [description field] = 'US Dollars' OR [description field] = 'Canadian Dollars' 

then [description field]

else [Row-1:new field]

endif

it should do a similar thing where you have a new column which is null/empty or has 'US Dollars'/'Canadian Dollars'  that you can use for filtering later on

[agodwin1250]

Certainly, probably should have provided this originally. The headers reference is highlighted blue, the identifiers highlighted green which I am trying to extract the rows from. Also FYI, I unfortunately need to pull in the entire file as other workstreams need other areas of information.

Edit: I had meant to unhighlight green the 2nd table, as I am not trying to pull from the CAD$. Apologies on the confusion

 

[atcodedog05]

Hi @agodwin1250 

Can you provide this excel which you have pasted. I can quickly prep you up a workflow.

[agodwin1250]

Awesome thanks! Let me get the actual numbers and sensitive data scrubbed first, should take only a few minutes here and I'll attach

[atcodedog05]

Hi @agodwin1250 

Give me the above blank it will do. Just want the template.

[hanykowska · Accepted solution]

Here's what I meant. Hope this helps!