Logic

[Zaid]

HI All,

I would request you to help me nail a logic. I have got three fields, x, cond1 and cond 2. the value or metric in x needs to be checked against the condition present in cond1 and cond2 and based on whichever matches correctly, i would need a new column say y, consisting of the condition it satifies, i.e. either cond1 or cond2 or N/A. Please refer to the below mentioned input and desired output file.

Sample Input:

x	cond1	cond2
100	>=99	>=100
100	<=100	<=80
56	>6	>55
100$	>=54$	>=99$
80	>81	>100

Desired output

x	cond1	cond2	y
100	>=99	>=100	cond2
100	<=100	<=80	cond1
56%	>6%	>55%	cond2
100$	>=54$	>=99$	cond2
80	>81	>100	N/A

[Zaid]

can someone please look into it and let me know if there are any concerns regarding the question

[MarqueeCrew]

After I finish my breakfast I will post.

Cheers,

Mark

[LordNeilLord]

@MarqueeCrew

What are you having for breakfast?

[LordNeilLord · Accepted solution]

@Zaid

Here's my attempt

[MarqueeCrew]

Eggs and salmon. I'm part way done but will 🛑 if you've got it done. 🙏

Cheers,

Mark

[Zaid]

That worked wonders @LordNeilLord. Incase I have further questions i will keep you posted. 

Thanks again

[Zaid]

@LordNeilLord there are a few concerns with the parsing. Actually my data has some values in decimals as well which the current regex expression is unable to read. Also there are values where after the operators there are symbols..like (>=$56)

So can you please provide me a regex which will be able to parse decimals as well, along with the types i have mentioned above.

So basically the types of values are (

>=56

<=87.5

>=100%

<$6

>=98MM,etc

So can you please provide me a regex to parse all these types of value.

Thanks

Zaid

[Zaid]

Bringing @MarqueeCrew in the loop as well to pitch in.

[LordNeilLord]

Hey @Zaid

Try this instead

I've added a formula to strip out anything you dont want using regex ([A-Za-z$%£<>=])....you can add more characters to this if you need to remove these to