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 DateUnique IDSentItemPairDesired Output11/27/22 12:00 AM13543YesBlueberries1Error- Apples should be sent- NOT Blueberries11/27/22 12:00 AM56765NoApples1Error- Apples should be sent- NOT Blueberries11/27/22 12:08 AM86543YesGrapes2OK11/27/22 12:08 AM26354NoPeaches2OK11/27/22 12:12 AM57345YesApples3Error- Two Apples sent11/27/22 12:12 AM67676YesApples3Error- Two Apples sent11/27/22 12:14 AM87657YesPineapples4OK11/27/22 12:14 AM85756NoLemon4OK11/27/22 12:15 AM76577NoOrange5OK11/27/22 12:15 AM45466NoBananas5OK11/27/22 12:16 AM65465YesApples6OK11/27/22 12:16 AM87676NoOrange6OK11/27/22 12:16 AM46546NoApples7Error- Apples should be sent- NOT Grapes11/27/22 12:16 AM57756YesGrapes7Error- Apples should be sent- NOT Grapes11/27/22 12:17 AM57657NoApples8OK11/27/22 12:17 AM13456YesApples8OK11/27/22 12:20 AM54765YesBananas9Error- Apples should be sent- NOT Bananas11/27/22 12:20 AM98798NoApples9Error- Apples should be sent- NOT Grapes11/27/22 12:24 AM88776YesGrapes10OK11/27/22 12:24 AM88567NoGrapes10OK11/27/22 12:27 AM69879YesLemon11Error- Two Lemons were sent11/27/22 12:27 AM59889YesLemon11Error- Two Lemons were sent11/27/22 12:28 AM87989NoPeaches12OK11/27/22 12:28 AM09809YesApples12OK11/27/22 12:29 AM05465NoLemon13OK11/27/22 12:29 AM76489YesApples13OK11/27/22 12:30 AM98768NoApples14Error- Apple should be sent- NOT Lime11/27/22 12:30 AM65880YesLime14Error- Apple should be sent- NOT Lime

Compare and Flag both Pairs based on Multiple Criteria

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

Interface Tools

Accepted answers

MatthewO

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

AppleFlags.yxzp

All comments

MatthewO

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

AppleFlags.yxzp

binu_acs

@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')))))))

MultiRow_1201.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703