Compare two rows and remove them

[bleu]

I've asked a similar question in the past but for some reason I can't figure this one out. I'm using multirow formulas but I'm not getting the right result and I can't see what I'm doing wrong.

If two times are the same but the milliseconds are +/-5 then I want to flag them for removal. (can filter it out later)

I'd like to do this:

1st multi row formula:

if [Time]=[Row-1:Time] and [Milliseconds]+5 >= [Row-1:Milliseconds] or
[Time]=[Row-1:Time] and [Milliseconds]-5 <= [Row-1:Milliseconds]

then 'Remove' else null() endif

2nd multi row formula

if [Time]=[Row+1:Time] and [Milliseconds]+5 >= [Row+1:Milliseconds] or
[Time]=[Row+1:Time] and [Milliseconds]-5 <= [Row+1:Milliseconds]

then 'Remove' else [New Field] endif

Can any one help?

[rfoster7]

I'm a little confused because your question isn't matching your example data. What I think you are wanting is "if two rows have the same time, then check the milliseconds. If there is MORE than 5 millisecond difference between them, then flag them for removal. If there are less than 5 milliseconds different then leave them unflagged"

If so then  assuming your data is sorted by time, then milliseconds, then you can definitely do it with a multi row formula

if [Time]=[Row-1:Time] and abs([Milliseconds] - [Row-1:Milliseconds]) > 5 then 'Remove' 

elseif [Time]=[Row+1:Time] and abs([Milliseconds] - [Row+1:Milliseconds]) > 5 then 'Remove'

else null()

endif

 I can't test that because you didn't provide the sample data in a way I could import it. Put it in as a table instead of a picture and it would help us to help you.

[binuacs]

@bleu One way of doing this with the help of Tile and MultiFormula tool

[bleu]

rfoster7 and binuacs thank you both for your solutions!

I tried both of them and they worked but I forgot to mention that the data is in pairs.

When there are 4 times that are exactly the same (with less than 5 milliseconds difference), they should not be removed if that makes this even more confusing!

I've attached some data in Excel format to explain it.

I'm stuck again.

[DataNath · Accepted solution]

This seems to do the trick for the dataset and expected outcome you've provided. Workflow attached!

[bleu]

Ok DataNath, I never would have figured that out. It works perfectly. 

THANK YOU!

😁