Filtering for duplicate values only if one of those values is negative

[NicC]

Hey folks,

I've got tens of thousands of rows of transaction data, and due to the accounting system it comes from, there are pairs of data I need to remove. However, I'm struggling to specify that pairs of rows should only be deleted where one of them are negative.

I've tried the unique tool, but in some cases, there are 4 entries of $10 and 1 entry of -$10. Only 1 of the positive and 1 of the negative should be deleted. You can see an example below:

P.S. I just noticed there's a table tool I could have used. Hopefully my formatting works. X_X

E.g. scenario - want to remove the red
expense code              amount     absolute value    key (category + abs + account code)               count of absolute value
12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5

12345-67                    10             10                        Meal1091110-67                                               5
12345-67                    -10            10                        Meal1091110-67                                               5
12345-67                    43              43                       Meal4391110-67                                               1
12345-67                    40              40                       Auto4091110-67                                                1

12345-67                    100            100                     Airfare10091110-67                                           2
12345-67                    -100           100                     Airfare10091110-67                                           2

12345-67                    -90             90                       Meal9091110-67                                                5

[AbhilashR]

Hi @NicC, you could use the multi-row formula tool to identify the records that needs to be filtered. I have attached a prototype for you to build upon.

Let us know if this isn't what you are looking for.

[zajaccount · Accepted solution]

Hi

maybe not the cleanest solution but I think I managed to do this using 2x multi row formulas.

Please let me know if this works for you

[NicC]

Thank you very much!! I still need to tweak it a little, but this got me 90% of the way there and will save me a ton of time!

[NicC]

Thanks for your response Abhilash! This got me nearly there. Ultimately, it needed the sort tool from the other response.

I appreciate your help!