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:
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?
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?
