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!
Solved! Go to Solution.
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
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!
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
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
Hi @agodwin1250
Can you provide this excel which you have pasted. I can quickly prep you up a workflow.
Awesome thanks! Let me get the actual numbers and sensitive data scrubbed first, should take only a few minutes here and I'll attach