Multi-Row Formula Tool

Using the Multi-Row Formula Tool Expression:

IF [ID]=[Row+1:ID] AND [Date1=[Row+1:Date1: THEN 0 ELSE 1 ENDIF

The intended result would be a 0 in New Field for both A1234 entries (rather than a 1 for the second New Field entry of A1234) ... can someone help me as to what I am doing incorrectly?

Thank you

example.multi-row.xlsx

exampleMultiRowQuestion.xlsx

Data Investigation

Accepted answers

CoG

As a note @binu_acs - solution will work fine for the data set provided, but will not work if you have an [ID] with multiple dates, including some that match and some that do not match. In addition, if you have [ID]'s associated with only one date, then that method won't quite work either, and would need further fine tuning.

All comments

bh1789

please ignore the first attachment example.multi-row.xlsx and instead view the 2nd attachment "exampleMultiRowQuestion.xlsx" thank you

TimN

I think you need to look back rather than forward.

IF [ID]= [Row-1:ID] AND [Date1]= [Row-1:Date1] THEN 0 ELSE 1 ENDIF

If you look forward then the 2nd A1234 will be compared to the first B2234. Also, since the first row has nothing to look back to I defaulted row one. See attached.

multi.yxmd

TimN

Are you setting the parameter for Rows that don't exist?

multi-row.bmp

binu_acs

@bh1789 One way of doing

Multi_Row_281123.yxmd

CoG

If you want all values that have the same Date1 and ID to return 0 then you will need to sort the list by ID and Date1, then use both your condition and @TimN 's condition combined to check the next and previous rows:

IF ([ID]=[Row+1:ID] AND [Date1]=[Row+1:Date1]) OR ([ID]=[Row-1:ID] AND [Date1]=[Row-1:Date1]) THEN 0 ELSE 1 ENDIF

Multi-Row Tool Sample Workflow 2.yxmd

CoG

bh1789

Thank you very much for your assistance. I do have additional conditions as you mentioned (in attached file). If you have time to assist I would be very grateful.

Thank you,

example_MultiRow_2023_11_29.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703