+ Reply to Thread
Results 1 to 6 of 6

Matching negative and positive reconciliation

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    61

    Matching negative and positive reconciliation

    Hi all,

    I need a simple way to highlight all the numbers in a column which pair the exact negative and positive. However, the remove duplicate or the ASAP utilities highlight duplicate button just don't seem to do the trick. Reason it fail is it will highlight all duplicates.

    In situation where I have two -1300 and one 1300. It will highlight all 3, where I just need them to highlight the first two cell that show -1300 and 1300 and leave the next -1300 alone.

    Is it possible to do this just with conditional formatting instead of some funky macros?

    Thanks,
    Ray

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Matching negative and positive reconciliation

    Here's a simple formulas play to achieve it ...
    Assume your source values are running in A2 down
    In B2: =IF(A2="","",IF(A2<0,-A2&"_"&COUNTIF(A$2:A2,A2),A2&"_"&COUNTIF(A$2:A2,A2)))
    In C2: =IF(COUNTIF(B:B,B2)=2,"x","")
    Copy B2:C2 down to the last row of source data
    Col C will flag all matching / knock-off items as "x"
    which may include multiple matches/knock-offs for the same pair of opposite values, if any

    Now, you can either just filter on col C for "x" to retrieve the whole lot of matching values (Knock-off values) at one go.
    Alternatively, do a CF on the data in col A, pointing to col C
    Eg, select data in A2:A100 (with A2 active), apply CF using formula: =$C2="x"
    Choose the formatting desired, ok out

    ps: between the 2 actions: Filtering and CF, I'd think that the filtering option usually proves to be more useful for any downstream actions
    ------------
    Success? Celebrate it, click the little star at the bottom left of my response(s)

  3. #3
    Registered User
    Join Date
    09-10-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    61

    Re: Matching negative and positive reconciliation

    PERFECT!
    It's truly brilliant how you make use of the "_" underscore and keep count the number of pairs that have occured.

    Thanks alot, you not only save my day but my colleagues too!

  4. #4
    Registered User
    Join Date
    05-14-2016
    Location
    Dar es salaam, Tanzania
    MS-Off Ver
    2010
    Posts
    1

    Re: Matching negative and positive reconciliation

    hi Max,

    can you help me to understand what do you mean when you say,"Copy B2:C2 down to the last row of source data" kindly help as I have the same problem.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,069

    Re: Matching negative and positive reconciliation

    Hi, welcome to the forum

    Forum rules require you to ask your questions in your own thread (and reference this 1 if you feel it would help)

    On the assumption that that phrase was used somewhere in this thread, it means that you are to copy the contents of B2 and C2 down to where your data ends - if the last ro of data is roww 100, that is how far you copy it down
    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

  6. #6
    Registered User
    Join Date
    10-25-2016
    Location
    CHENNAI
    MS-Off Ver
    2013
    Posts
    1

    Re: Matching negative and positive reconciliation

    Thanks. It worked,Helped me to reconcile.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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