+ Reply to Thread
Results 1 to 13 of 13

Find and highlight two opposite numbers by multiple criteria(A+B=0)

  1. #1
    Registered User
    Join Date
    05-18-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Hello I need your help again

    I have been strugling with this problem for really long and now I hope someone will help me.

    I have really long sheet populated with various data (one or more are populated with numbers) and I have to find two opposite data and delete/cut them to another sheet. So I would like to make macro that do this
    - find two numbers in one column that sum of those two are 0 (lets say it is column B and I have to find two numbers which sum is 0 (a+b=0; 50+(-50) = 0)
    - then I have to check if those two numbers have same data in another column (column A)
    - if this is OK (sum is 0, and data in column A is same) then I have to delete/cut those two rows and past them in another sheet (in same workbook)

    It would be great, if macro could let user to chose in which column are first numbers (which I sum them) and in which column is second data.

    I hope I wrote my problem with enough details and I hope there is solution for my problem

    Thanks for any help!

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Can you provide an example (excel file)?
    Do you want to delete the rows or just the cells in Column B? If you delete the rows, you delete different rows in column A.
    Click on the star if you think I helped you

  3. #3
    Registered User
    Join Date
    05-18-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    In attached example I would like for macro to sum column D and if sum of two cells is 0 then check if cells in B are the same and if they are same copy thow two rows from sheet 1 to sheet 2 nad then delete those two rows in sheet 1. Then macro would continue with that on sheet 1 until no more sums would be 0. >If macro would found more than 1 example of cells to be 0, macro would copy

    In attached excel there are 3 sheets.
    1st sheet is "Example" and is database
    2nd sheet is "Solution" where are database without rows that match criteria
    3th sheet is "Criteria" where are rows that matched the criteria in macro

    As I allready said It would be great if there would be possible for user to select where are data (in example are in D and in B)
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    You actually want more than you described. You want to check for opposite numbers for the same person.. I will look at it.

  5. #5
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Try the attached.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    bumbar

    The attached should do the job.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-18-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Millz:
    Your macro is great and do the job, but when useing this macro I discovered something that I did not know would be an issue. If there are three entries (two +50 and one -50) your macro would copy all three to "criteria" and none to "solution" sheet. For my purpose I need to copy one (pozitive value in this case) to "solution" sheet and two (positive and negative value so sum is 0) to "criteria" sheet.

    Jindon:
    your macro is great and copy to solution only one pozitive value (from same example as mention before in this post) but is another proble because I cant select column to sum and column to use as criteria. Also It would be great if there would be delete rows in another sheet - "criteria"

    I tried to write macro from both examples but I could not do it. So please help me a little more if it is possible
    Last edited by bumbar; 12-13-2013 at 01:17 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    My code requires all 4 columns to calculate,
    When input box appears, just select any cell in each corresponding column.
    You don't need to select whole column.
    Attached Files Attached Files

  9. #9
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Added another prompt to select No. column. This column should contain unique running number or ID like column A in the sample file.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-18-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    You two are great Now I have macro who do everything I want and more Thank you both

  11. #11
    Registered User
    Join Date
    05-18-2012
    Location
    Slovenia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Guys I need more help....I tried to do it myself with but I could not do it...It is simply to much for me ...

    Jindon...I prefer your code, because its fast ...do the job in few seconds ...but there is problem..now my base has more columns and now code does not work right. Code is made to work with 5 columns but now I have 8 or more (it is different number of columns every time) and it copy results OK to 5th column but than from 6th column on it copy wrong date. I hope anyone can rewrite this code to work with different number of columns each time...

    Here is file in which it is code and the problem if the base have more than 5 columns. In sheet Example is database and in sheet solution is result. Here you can see that column F in not copied OK because it is not same as column A....I don know why I can not upload file...but if you use one from latest jidon code, copy column A to F and run macro you can see what is bothering me...

  12. #12
    Registered User
    Join Date
    05-25-2017
    Location
    France
    MS-Off Ver
    2016
    Posts
    1

    Thumbs up Excel 2016 - Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Hi everyone !

    I turned out to have the exact same problem as Bumbar and I tried to used both millz and jindon's codes.

    I don't know if I either

    - don't manipulate the macros in the right the right way. What criteria should I enter when clicking the running macro box ? I chose column D and B.

    - don't have a version allowing the 2007 excel macro to work ( I am on a 2016 Excel version)

    Thank you in advance for your helps guys,
    Margaux

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Find and highlight two opposite numbers by multiple criteria(A+B=0)

    Margaux welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 09-14-2012, 08:13 PM
  2. Automatically find and highlight common numbers in multiple coulmns.
    By cyber_9 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 06-22-2012, 10:34 AM
  3. Replies: 9
    Last Post: 06-29-2009, 01:14 AM
  4. Find and Highlight specific numbers
    By RWheldon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2009, 07:34 AM
  5. Find and Highlight a group of numbers
    By Krazy Kasper in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2008, 04:17 PM

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