+ Reply to Thread
Results 1 to 4 of 4

Amounts that cancel out each other

  1. #1
    Registered User
    Join Date
    10-19-2011
    Location
    NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Amounts that cancel out each other

    Please Login or Register  to view this content.
    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.

  2. #2
    Forum Moderator
    Join Date
    05-14-2009
    Location
    RDC
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    7,039

    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.

  3. #3
    Registered User
    Join Date
    10-19-2011
    Location
    NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    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.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    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:
    Please Login or Register  to view this content.
    If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.

    If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1