Evaluating multiple rows across unique ID

[mystasz]

Hi Community,

For the below given dataset, I'd like to evaluate the "value" column by "ID" and assign a violation. 

Below is the logic:

- Create a new column called "Violation"

- If ID = ST01 and value is 1 AND 4 - Label as "Violation 1"

- If ID = ST01 and value is 2 AND 7 - Label as "Violation 2"

- If ID = ST02 and value is 1 AND10 - Label as "Violation 3"

- If ID = ST02 and value is 2 AND 3  - Label as "Violation 4"

- If ID = ST02 and value is 6 AND 9 - Label as "Violation 5"

Sample and expected data attached. 

[messi007]

@mystasz,

Please see below :

Attached the workflow,

Regards,

[mystasz]

Hello and thank you. The formula used is applying "OR" logic and evaluating each row individually. For 1 AND 4, this logic would say its a violation if only 1 existed and not 4. Both 1 and 4 need to exist for a single ID. 

If you change the number 4 to a 20 for ST01, it's still assigning Violation 1 which would be untrue based on the logic. 

[ArtApa · Accepted solution]

Hi @mystasz - Here is how you can achieve a desired outcome:

[mystasz]

This worked perfectly! Thank you

[Qiu · Accepted solution]

@mystasz 
@ArtApa gives a very smart solution, but I would also like to presenta pretty length one but I believe which is more straightforwad and maybe easy to modify when you have some input changes.
I used two Batch Macro to list up the Combination of Values for each ID and name then do the conditional checking.