Multi-Row Formula Tool

[bh1789]

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

[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.

[bh1789]

If I use the logic you suggested then I get just the opposite, the first occurrence of ID A1234 has a new field of 1 and the second occurrence has a new field of 0 ... I need both to be a 0 so I can drop them both

[TimN]

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

[binuacs]

@bh1789 One way of doing

[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

[CoG · Accepted solution]

As a note @binuacs - 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.

[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,