Filter on rows which are between two strings, but are in different columns

[Davonalt]

Filter on rows which are between two strings, but are in different columns. I have combined several data steams and now i want to filter on the data between two fields. The fields are in bold.

Field 1	Field 2	Field 3	Field 4	Field 5	Field 6	Field 7	Field 8	Field 9
Excluded In Returns
A	ONE	Mock	1111	COMPANY	5	7		n
B	TWO	Mock	2222	COMPANY	6	8		y
Subtotal								for Excluded in Returns

  The output would then be..

A	ONE	Mock	1111	COMPANY	5	7		n
B	TWO	Mock	2222	COMPANY	6	8		y

[OllieClarke · Accepted solution]

Hi @Davonalt 

You can use the multi-row formula here to create a flag field which you can then filter on

we can check whether the row before contains excluded in returns (which is case insensitive) to turn the flag on, and check if the current field 9 contains it to turn the flag off.

If contains([row-1:Field 1],'excluded in returns') THEN 1
ELSEIF contains([field 9],'excluded in returns') THEN 0
ELSE [row-1:flag]
ENDIF

 Hope that helps,

Ollie

[Davonalt]

Thank you.