Matching positive and negative values

[Ressa91]

Hi everyone

I've been given a task where I need to reconcile accounts to find the imbalances. I need to match "pair" positive and negative values that adds up to zero.

I'm looking for a formula that will highlight and move the matches to another sheet, so that I'm only left with duplicates or values that doesn't add up to zero.

See example below

Is this something someone here can help me with if possible? The data set is huge and doing manually is time consuming.

Appreciative of the feedback!
Thank you,

 

[Akuini]

1. So, you're trying to match the numbers only within the same group, based on the value in column B?
2. How many rows of data are there at maximum in one group?
3. In your example in post 1, the records for 1-May-2021 in col B are actually different. How is the code supposed to know that they are meant to be grouped together?

 

[Ressa91]

1. So, you're trying to match the numbers only within the same group, based on the value in column B?
2. How many rows of data are there at maximum in one group?
3. In your example in post 1, the records for 1-May-2021 in col B are actually different. How is the code supposed to know that they are meant to be grouped together?

The data I'm working with are journal entries (If you look at column C which is a Balancing Entity(0140) and Column D is Related Party(0142)). So in order for the books to balance an opposite entry is created which brings me to a zero balance, so I'm looking for a formula or code that can match the positive and negative "Identical" values that adds up to zero like the example for 1-May-2021 figures are identical so column B doesn't have to be exactly identical given the explanation above.


I'm also looking for a formula or code that combines 2 or more positive values that adds up to a negative total which together would give me zero
see example below

 

[Akuini]

1. So, is the group actually relevant only for finding a match of one number against multiple numbers?
2. You didn't my answer question no 2:
How many rows of data are there at maximum in one group?

 

[Ressa91]

1. So, is the group actually relevant only for finding a match of one number against multiple numbers?
2. You didn't my answer question no 2:
How many rows of data are there at maximum in one group?

yes that's correct

there are about 2232 maximum data in a group

 

[Akuini]

there are about 2232 maximum data in a group

I was hoping you would say something like "no more than 10".
Trying to find a match of one number against multiple numbers (that returns zero) requires checking all combination one by one until we find a match. To do that on thousands of data could crash Excel.
So, I'm sorry couldn't help you with this. Hope others could help you.