IF conditions with Filter

Question

I am looking to perform a filter based on multiple conditions.

If doc number contains 380 and 225 then return the line containing 380

If only 225 in doc type then return the line.

Example data source attached.

If anyone can suggest a custom formula to use within the filter tool or if another tool is more appropriate?

Thank you in advance.

Filter on Reference and Doc Type.xlsx

Emil_Kos · Accepted Answer

Hi @tom05,

I have prepared a workflow for you:

The output:

Kenda · Accepted Answer

Hi @tom05

I solved this using a Multi-Row Formula tool. First I sorted the data by Reference - Ascending then Doc Type - Descending. Then I used the following expression in my Multi-Row Formula tool, grouping by Reference:

iif([Doc Type]=380 && [Row+1:Doc Type]=325, "Yes", iif([Doc Type]=325 && [Row-1:Doc Type]!=380, "Yes", "No"))

Hope this helps!

Maskell_Rascal · Accepted Answer

Hi @tom05

Depending on how the rest of your data looks, I have two different solutions for you.

The first one leverages the Sample Tool after sorting the data by Reference(Ascending) and Doc Type(Descending).

The second method creates groups based on Reference after sorting, and then a Filter tool selects the first record of every group.

Attached is the workflow.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks,

Phil