Looking for a genius: Match Debits to Multiple Credits or vise versa

[erinfleener]

Hello,

I am trying to see if there is a way (I'm doubting whether this is even possible anymore) to highlight or mark items that can offset each other exactly given certain criteria (for this purpose Project and Fund Column B and C). For example: There could be 250, 250, 500 that would 0 out with a -1000 of the same project code however there may be another -1000 that doesn't have anything it can be offset with since the only other option was already offset. In other words there are duplicates.

I already have a formula that I use that will match everything that has an exact opposite match but I was wondering if there is a way to take it a step further and match items that are not exact. I put a little snapshot of different scenarios below for better explanation

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Project[/TD]
[TD]Fund[/TD]
[TD]Amount[/TD]
[TD]Match[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2017[/TD]
[TD][TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]P0001111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1000[/TD]
[TD]-100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2017[/TD]
[TD]P0001111[/TD]
[TD]1000[/TD]
[TD]-100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2017[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/1/2017[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/23/2016[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]250[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/23/2016[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/23/2016[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]-1000[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/22/2016[/TD]
[TD]P0001111[/TD]
[TD]2000[/TD]
[TD]-1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/23/2016[/TD]
[TD][TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: #B4C6E7"][/TD]
P0003244[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2000[/TD]
[TD]600[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/23/2016[/TD]
[TD]P0003244[/TD]
[TD]2000[/TD]
[TD]600[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2017[/TD]
[TD]P0003244[/TD]
[TD]2000[/TD]
[TD]-1200[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2017[/TD]
[TD]P0003244[/TD]
[TD]2000[/TD]
[TD]-1200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2017[/TD]
[TD]P0003244[/TD]
[TD]2000[/TD]
[TD]-300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2017[/TD]
[TD]P0003244[/TD]
[TD]2000[/TD]
[TD]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11/12/2017[/TD]
[TD][TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: #F8CBAD"]P0005555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1000[/TD]
[TD]-10[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]2000[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]1000[/TD]
[TD]340[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]1000[/TD]
[TD]-105[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]1000[/TD]
[TD]-50[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]3000[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]1000[/TD]
[TD]-175[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD]P0005555[/TD]
[TD]3000[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5/2/2016[/TD]
[TD][TABLE="width: 48"]
<colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]P0007070[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3000[/TD]
[TD]-150[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]