Amounts that cancel out each other

Code:

Key Amount Refund Comment

101 -90.00 Cancel

101 90.00 Cancel

102 90.00 Cancel

102 -30.00 Cancel

102 -60.00 Cancel

103 90.00 Cancel

103 -90.00 Cancel

103 -25.00

103 40.00

104 45.00 Cancel

104 15.00 Cancel

104 45.00

104 -60.00 Cancel

104 50.00

105 35.00

105 -40.00

106 80.00

107 -20.00

I have the above as desired output. Initially, the Comment column is empty. The goal is to tag a row 'Cancel' under the Comment column if the rows have the same key and there is/are 'Amount' that cancel out with the 'Refunds'. In the above example, the rows with key 101 cancel out each other straightforward so we tag those rows as 'Cancel'. For rows with key 102, the amount of 90 cancels out with the sum of -60 and -30 so we tag those as 'Cancel'. For rows with key 103, the amount of 90 cancels out with Refund -90 so they're tagged as 'Cancel'. But the amount of 40 and refund of -25 do not exactly cancel out each other so we do not tag them as Cancel. For rows with key 104, the amounts 45 and 15 cancel out with refund -60 so we tag them as Cancel - we did not tag as 'Cancel' the other row with amount 45 and 50. For rows with key 105, since the amount and refund values do not cancel out each other so we do not tag them. Rows with keys 106 and 107 need not be tagged as 'Cancel' too because they each have only one record. I appreciate your help.

Re: Amounts that cancel out each other

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a **BEFORE** sheet and an **AFTER** sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

1 Attachment(s)

Re: Amounts that cancel out each other

I have the attached file with the BEFORE and AFTER tabs. The AFTER tab shows the desired output. Initially, in the BEFORE tab, the Comment column is empty. The goal is to tag a row 'Cancel' under the Comment column if the rows have the same key and there is/are 'Amount' that cancel out with the 'Refunds'. In the above example, the rows with key 101 cancel out each other straightforward so we tag those rows as 'Cancel'. For rows with key 102, the amount of 90 cancels out with the sum of -60 and -30 so we tag those as 'Cancel'. For rows with key 103, the amount of 90 cancels out with Refund -90 so they're tagged as 'Cancel'. But the amount of 40 and refund of -25 do not exactly cancel out each other so we do not tag them as Cancel. For rows with key 104, the amounts 45 and 15 cancel out with refund -60 so we tag them as Cancel - we did not tag as 'Cancel' the other row with amount 45 and 50. For rows with key 105, since the amount and refund values do not cancel out each other so we do not tag them. Rows with keys 106 and 107 need not be tagged as 'Cancel' too because they each have only one record. I appreciate your help.

Re: Amounts that cancel out each other

Here's a formula that can get any results where the totals for a given key cancel each other, or there's a specific numerical match for a given key. I'm not sure how to deal with situations like key 104, but this might spark some ingenuity from someone else:

Code:

`=IF(OR(SUM(SUMIF($A$2:$A$19,$A2,$B$2:$B$19),SUMIF($A$2:$A$19,$A2,$C$2:$C$19))=0,IF(ISNUMBER($C2),SUMPRODUCT(($A$2:$A$19=$A2)*($B$2:$B$19=ABS($C2))),IF(ISNUMBER($B2),SUMPRODUCT(($A$2:$A$19=$A2)*($C$2:$C$19=($B2*-1))),0))>0),"Cancel","")`