many to many matching?

Ash1432

New Member
Joined
Apr 20, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I have a set of data and I need to go through and find things that offset each other and mark them to be cleared. I use a sumif equation based on the reference number to find the matches that are one to one. But I sometimes have matches that are many to one or many to many and not all of the items will be included in the clear. Here is my example:

1618951911620.png


In the above, rows 2 and 3 have the same reference and sum to 0, so they would have some sort of positive indicator in the Clear? column that they should be matched together (I am currently using if(sumif) for this scenario). For reference 3075437 rows 4 and 5 would have positive indicators in the Clear? column as they sum to zero, but not row 6. I don't want to match row 5 and row 6 together since they have the same date. This is the many to one scenario. For reference 3128382, row 7 matches with rows 9 and 10 as they sum to zero and row 8 would not have a match.

I don't want to use VBA,since I don't know enough to trouble shoot it if something goes wrong. Is there a way I can accomplish this with a series of equations?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top