Compare rows of like values

[Dittau]

This one is a tough one. I have tons of rows i have to compare and there are similar data points so concatenation is very difficult:

Scenario: how do i take the below data and have it recognize that row 1 and 3 are a match I want to remove (anytike column B is opposite, column C matches and column d is the same remove the match) but then keep row 2, 4 & 5 to continue traveling down the flow

columns:  A         B          C             D                   E 

Row1     Apple    -4         5.21      Grocery      Produce

Row 2    Apple     4          5.18      Grocery      Fruit

Row 3    Apple      4         5.21     Grocery      Tree

Row 4    Apple      4         5.12      Grocery      Seed

Row 5    Apple     4        5.21    Grocery     Salad

[K_Vinogradov]

Hi @Dittau!

I believe your problem can be solved by using the sort tool and the multi-row tool. We can sort by column A, C and D, and then use a multi-row formula to identify matches by using the logic you provided.

Spoiler

Let us know if it works!

[aatalai]

Based on your logic should row 5 also be dropped? As it is a match with row 1?

If so this attached workflow might help

[Dittau]

Nope, need to keep row 5. Duplicate rows need to stay as column E is unique 

[aatalai]

@Dittau for row 1 and 3 column e are different and they are drooped

[Dittau]

This is close. How would you add a $50 tolerance to the price column to match on.

lets say column B is a 4 and -4 and then column C is 49.50 different but i want there to be a $50 matching threshold if column C is within $50 and if column B is up down

Also update column D to R and D and just say if column A matches and column B and D are opposite and column C is within $50 the match true

below i would want row 1 to match with row 2 or 3 and then the row that doesn’t match (2 or 3) to continue on the flow - also note row 6 is the same as row 2 but only 2 wors can match off so row 6 needs to continue down the path as well. Additionally row 4 and 5 would be a perfect match and should match as well so at the end of the multi row formula and filter only 1 row would continue on and the other 4 would be matched

columns:  A         B          C             D                   E 

Row1     Apple    -4         5.21        R      Produce

Row 2    Apple     4         55.01      D      Fruit

Row 3    Apple      4        54.21      D      Tree

Row 4    Apple    -4        5.12         R       Seed

Row 5    Apple     4         5.12         D     Salad

Row 6    Apple      4        55.01       D        Cake