Compare two rows and remove them

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?

Developer

Accepted answers

DataNath

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

Paired removal times.yxmd

All comments

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.

binu_acs

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

compare_rows_05_05.yxmd

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.

Flag Rows for Removal.xlsx

DataNath

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

Paired removal times.yxmd

bleu

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

THANK YOU!

😁

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703