Compare and Flag both Pairs based on Multiple Criteria

[bleu]

I need help with this one, I'm going in circles.

I need to compare two rows (they're already paired up with a multi row formula)

The criteria is:
- If the pair has an Apple in it, the Apple should ALWAYS be sent, the other fruit should NOT be sent
- If the SAME fruit is in a pair (even it's an Apple), only one should be sent

- For fruit other than Apples it's OK if both are not sent

- I need the output to show both pairs like in the desired output below

Date	Unique ID	Sent	Item	Pair	Desired Output
11/27/22 12:00 AM	13543	Yes	Blueberries	1	Error- Apples should be sent- NOT Blueberries
11/27/22 12:00 AM	56765	No	Apples	1	Error- Apples should be sent- NOT Blueberries
11/27/22 12:08 AM	86543	Yes	Grapes	2	OK
11/27/22 12:08 AM	26354	No	Peaches	2	OK
11/27/22 12:12 AM	57345	Yes	Apples	3	Error- Two Apples sent
11/27/22 12:12 AM	67676	Yes	Apples	3	Error- Two Apples sent
11/27/22 12:14 AM	87657	Yes	Pineapples	4	OK
11/27/22 12:14 AM	85756	No	Lemon	4	OK
11/27/22 12:15 AM	76577	No	Orange	5	OK
11/27/22 12:15 AM	45466	No	Bananas	5	OK
11/27/22 12:16 AM	65465	Yes	Apples	6	OK
11/27/22 12:16 AM	87676	No	Orange	6	OK
11/27/22 12:16 AM	46546	No	Apples	7	Error- Apples should be sent- NOT Grapes
11/27/22 12:16 AM	57756	Yes	Grapes	7	Error- Apples should be sent- NOT Grapes
11/27/22 12:17 AM	57657	No	Apples	8	OK
11/27/22 12:17 AM	13456	Yes	Apples	8	OK
11/27/22 12:20 AM	54765	Yes	Bananas	9	Error- Apples should be sent- NOT Bananas
11/27/22 12:20 AM	98798	No	Apples	9	Error- Apples should be sent- NOT Grapes
11/27/22 12:24 AM	88776	Yes	Grapes	10	OK
11/27/22 12:24 AM	88567	No	Grapes	10	OK
11/27/22 12:27 AM	69879	Yes	Lemon	11	Error- Two Lemons were sent
11/27/22 12:27 AM	59889	Yes	Lemon	11	Error- Two Lemons were sent
11/27/22 12:28 AM	87989	No	Peaches	12	OK
11/27/22 12:28 AM	09809	Yes	Apples	12	OK
11/27/22 12:29 AM	05465	No	Lemon	13	OK
11/27/22 12:29 AM	76489	Yes	Apples	13	OK
11/27/22 12:30 AM	98768	No	Apples	14	Error- Apple should be sent- NOT Lime
11/27/22 12:30 AM	65880	Yes	Lime	14	Error- Apple should be sent- NOT Lime

[MatthewO · Accepted solution]

@bleu the attached workflow accomplishes the results you have shared above. I hope this is helpful for you!

[binuacs]

@bleu One way of doing this with the Multi-Row formula

IIF([Sent] = 'Yes' AND [Item] = 'Apples' AND [Row+1:Sent]='No', 'OK',
IIF([Sent] = 'Yes' And [Item] != 'Apples' AND [Row+1:Item] = 'Apples','Error- ' + [Row+1:Item]+' should be sent- '+ 'NOT '+ [Item],
IIF([Sent] = 'No' AND [Item] = 'Apples' AND !isNull([Row-1:Item]),'Error- Apples should be sent- NOT '+[Row-1:Item],
IIF([Sent] = 'No' AND [Item] = 'Apples' AND isNull([Row-1:Item]) AND [Row+1:Item] = 'Apples' AND [Row+1:Sent]='Yes','OK',
IIF([Sent] = 'No' AND [Item] = 'Apples' AND  isNull([Row-1:Item]),'Error- Apples should be sent- NOT '+[Row+1:Item],
IIF([Sent] = 'Yes' AND [Item] != 'Apples' AND !isNull([Row-1:Item]) AND [Row-1:Item] != [Item] ,'Error- Apples should be sent- NOT '+[Item],
IIF(([Sent] = 'Yes' AND  [Sent] = [Row+1:Sent] And [Item] = [Row+1:Item]) 
	OR ([Sent] = 'Yes' AND  [Sent] = [Row-1:Sent] And [Item] = [Row-1:Item] ),'Error - Two ' +[Item]+' Sent','OK')))))))
	

[bleu]

Thank you MatthewO! That works! 😁

binuacs, thank you- it's so concise. I love it! For some reason I couldn't get it to work when I changed the fruit to Grapes. I think I'm doing something wrong in that multi row.